1"""
   2## Expressions
   3
   4Every AST node in SQLGlot is represented by a subclass of `Expression`.
   5
   6This module contains the implementation of all supported `Expression` types. Additionally,
   7it exposes a number of helper functions, which are mainly used to programmatically build
   8SQL expressions, such as `sqlglot.expressions.select`.
   9
  10----
  11"""
  12
  13from __future__ import annotations
  14
  15import datetime
  16import math
  17import numbers
  18import re
  19import textwrap
  20import typing as t
  21from collections import deque
  22from copy import deepcopy
  23from enum import auto
  24from functools import reduce
  25
  26from sqlglot._typing import E
  27from sqlglot.errors import ErrorLevel, ParseError
  28from sqlglot.helper import (
  29    AutoName,
  30    camel_to_snake_case,
  31    ensure_collection,
  32    ensure_list,
  33    seq_get,
  34    subclasses,
  35)
  36from sqlglot.tokens import Token
  37
  38if t.TYPE_CHECKING:
  39    from typing_extensions import Literal as Lit
  40
  41    from sqlglot.dialects.dialect import DialectType
  42
  43
  44class _Expression(type):
  45    def __new__(cls, clsname, bases, attrs):
  46        klass = super().__new__(cls, clsname, bases, attrs)
  47
  48        # When an Expression class is created, its key is automatically set to be
  49        # the lowercase version of the class' name.
  50        klass.key = clsname.lower()
  51
  52        # This is so that docstrings are not inherited in pdoc
  53        klass.__doc__ = klass.__doc__ or ""
  54
  55        return klass
  56
  57
  58SQLGLOT_META = "sqlglot.meta"
  59TABLE_PARTS = ("this", "db", "catalog")
  60
  61
  62class Expression(metaclass=_Expression):
  63    """
  64    The base class for all expressions in a syntax tree. Each Expression encapsulates any necessary
  65    context, such as its child expressions, their names (arg keys), and whether a given child expression
  66    is optional or not.
  67
  68    Attributes:
  69        key: a unique key for each class in the Expression hierarchy. This is useful for hashing
  70            and representing expressions as strings.
  71        arg_types: determines what arguments (child nodes) are supported by an expression. It
  72            maps arg keys to booleans that indicate whether the corresponding args are optional.
  73        parent: a reference to the parent expression (or None, in case of root expressions).
  74        arg_key: the arg key an expression is associated with, i.e. the name its parent expression
  75            uses to refer to it.
  76        comments: a list of comments that are associated with a given expression. This is used in
  77            order to preserve comments when transpiling SQL code.
  78        type: the `sqlglot.expressions.DataType` type of an expression. This is inferred by the
  79            optimizer, in order to enable some transformations that require type information.
  80        meta: a dictionary that can be used to store useful metadata for a given expression.
  81
  82    Example:
  83        >>> class Foo(Expression):
  84        ...     arg_types = {"this": True, "expression": False}
  85
  86        The above definition informs us that Foo is an Expression that requires an argument called
  87        "this" and may also optionally receive an argument called "expression".
  88
  89    Args:
  90        args: a mapping used for retrieving the arguments of an expression, given their arg keys.
  91    """
  92
  93    key = "expression"
  94    arg_types = {"this": True}
  95    __slots__ = ("args", "parent", "arg_key", "comments", "_type", "_meta", "_hash")
  96
  97    def __init__(self, **args: t.Any):
  98        self.args: t.Dict[str, t.Any] = args
  99        self.parent: t.Optional[Expression] = None
 100        self.arg_key: t.Optional[str] = None
 101        self.comments: t.Optional[t.List[str]] = None
 102        self._type: t.Optional[DataType] = None
 103        self._meta: t.Optional[t.Dict[str, t.Any]] = None
 104        self._hash: t.Optional[int] = None
 105
 106        for arg_key, value in self.args.items():
 107            self._set_parent(arg_key, value)
 108
 109    def __eq__(self, other) -> bool:
 110        return type(self) is type(other) and hash(self) == hash(other)
 111
 112    @property
 113    def hashable_args(self) -> t.Any:
 114        return frozenset(
 115            (k, tuple(_norm_arg(a) for a in v) if type(v) is list else _norm_arg(v))
 116            for k, v in self.args.items()
 117            if not (v is None or v is False or (type(v) is list and not v))
 118        )
 119
 120    def __hash__(self) -> int:
 121        if self._hash is not None:
 122            return self._hash
 123
 124        return hash((self.__class__, self.hashable_args))
 125
 126    @property
 127    def this(self) -> t.Any:
 128        """
 129        Retrieves the argument with key "this".
 130        """
 131        return self.args.get("this")
 132
 133    @property
 134    def expression(self) -> t.Any:
 135        """
 136        Retrieves the argument with key "expression".
 137        """
 138        return self.args.get("expression")
 139
 140    @property
 141    def expressions(self) -> t.List[t.Any]:
 142        """
 143        Retrieves the argument with key "expressions".
 144        """
 145        return self.args.get("expressions") or []
 146
 147    def text(self, key) -> str:
 148        """
 149        Returns a textual representation of the argument corresponding to "key". This can only be used
 150        for args that are strings or leaf Expression instances, such as identifiers and literals.
 151        """
 152        field = self.args.get(key)
 153        if isinstance(field, str):
 154            return field
 155        if isinstance(field, (Identifier, Literal, Var)):
 156            return field.this
 157        if isinstance(field, (Star, Null)):
 158            return field.name
 159        return ""
 160
 161    @property
 162    def is_string(self) -> bool:
 163        """
 164        Checks whether a Literal expression is a string.
 165        """
 166        return isinstance(self, Literal) and self.args["is_string"]
 167
 168    @property
 169    def is_number(self) -> bool:
 170        """
 171        Checks whether a Literal expression is a number.
 172        """
 173        return isinstance(self, Literal) and not self.args["is_string"]
 174
 175    @property
 176    def is_int(self) -> bool:
 177        """
 178        Checks whether a Literal expression is an integer.
 179        """
 180        if self.is_number:
 181            try:
 182                int(self.name)
 183                return True
 184            except ValueError:
 185                pass
 186        return False
 187
 188    @property
 189    def is_star(self) -> bool:
 190        """Checks whether an expression is a star."""
 191        return isinstance(self, Star) or (isinstance(self, Column) and isinstance(self.this, Star))
 192
 193    @property
 194    def alias(self) -> str:
 195        """
 196        Returns the alias of the expression, or an empty string if it's not aliased.
 197        """
 198        if isinstance(self.args.get("alias"), TableAlias):
 199            return self.args["alias"].name
 200        return self.text("alias")
 201
 202    @property
 203    def alias_column_names(self) -> t.List[str]:
 204        table_alias = self.args.get("alias")
 205        if not table_alias:
 206            return []
 207        return [c.name for c in table_alias.args.get("columns") or []]
 208
 209    @property
 210    def name(self) -> str:
 211        return self.text("this")
 212
 213    @property
 214    def alias_or_name(self) -> str:
 215        return self.alias or self.name
 216
 217    @property
 218    def output_name(self) -> str:
 219        """
 220        Name of the output column if this expression is a selection.
 221
 222        If the Expression has no output name, an empty string is returned.
 223
 224        Example:
 225            >>> from sqlglot import parse_one
 226            >>> parse_one("SELECT a").expressions[0].output_name
 227            'a'
 228            >>> parse_one("SELECT b AS c").expressions[0].output_name
 229            'c'
 230            >>> parse_one("SELECT 1 + 2").expressions[0].output_name
 231            ''
 232        """
 233        return ""
 234
 235    @property
 236    def type(self) -> t.Optional[DataType]:
 237        return self._type
 238
 239    @type.setter
 240    def type(self, dtype: t.Optional[DataType | DataType.Type | str]) -> None:
 241        if dtype and not isinstance(dtype, DataType):
 242            dtype = DataType.build(dtype)
 243        self._type = dtype  # type: ignore
 244
 245    def is_type(self, *dtypes) -> bool:
 246        return self.type is not None and self.type.is_type(*dtypes)
 247
 248    def is_leaf(self) -> bool:
 249        return not any(isinstance(v, (Expression, list)) for v in self.args.values())
 250
 251    @property
 252    def meta(self) -> t.Dict[str, t.Any]:
 253        if self._meta is None:
 254            self._meta = {}
 255        return self._meta
 256
 257    def __deepcopy__(self, memo):
 258        copy = self.__class__(**deepcopy(self.args))
 259        if self.comments is not None:
 260            copy.comments = deepcopy(self.comments)
 261
 262        if self._type is not None:
 263            copy._type = self._type.copy()
 264
 265        if self._meta is not None:
 266            copy._meta = deepcopy(self._meta)
 267
 268        return copy
 269
 270    def copy(self):
 271        """
 272        Returns a deep copy of the expression.
 273        """
 274        new = deepcopy(self)
 275        new.parent = self.parent
 276        return new
 277
 278    def add_comments(self, comments: t.Optional[t.List[str]]) -> None:
 279        if self.comments is None:
 280            self.comments = []
 281        if comments:
 282            for comment in comments:
 283                _, *meta = comment.split(SQLGLOT_META)
 284                if meta:
 285                    for kv in "".join(meta).split(","):
 286                        k, *v = kv.split("=")
 287                        value = v[0].strip() if v else True
 288                        self.meta[k.strip()] = value
 289                self.comments.append(comment)
 290
 291    def append(self, arg_key: str, value: t.Any) -> None:
 292        """
 293        Appends value to arg_key if it's a list or sets it as a new list.
 294
 295        Args:
 296            arg_key (str): name of the list expression arg
 297            value (Any): value to append to the list
 298        """
 299        if not isinstance(self.args.get(arg_key), list):
 300            self.args[arg_key] = []
 301        self.args[arg_key].append(value)
 302        self._set_parent(arg_key, value)
 303
 304    def set(self, arg_key: str, value: t.Any) -> None:
 305        """
 306        Sets arg_key to value.
 307
 308        Args:
 309            arg_key: name of the expression arg.
 310            value: value to set the arg to.
 311        """
 312        if value is None:
 313            self.args.pop(arg_key, None)
 314            return
 315
 316        self.args[arg_key] = value
 317        self._set_parent(arg_key, value)
 318
 319    def _set_parent(self, arg_key: str, value: t.Any) -> None:
 320        if hasattr(value, "parent"):
 321            value.parent = self
 322            value.arg_key = arg_key
 323        elif type(value) is list:
 324            for v in value:
 325                if hasattr(v, "parent"):
 326                    v.parent = self
 327                    v.arg_key = arg_key
 328
 329    @property
 330    def depth(self) -> int:
 331        """
 332        Returns the depth of this tree.
 333        """
 334        if self.parent:
 335            return self.parent.depth + 1
 336        return 0
 337
 338    def iter_expressions(self) -> t.Iterator[t.Tuple[str, Expression]]:
 339        """Yields the key and expression for all arguments, exploding list args."""
 340        for k, vs in self.args.items():
 341            if type(vs) is list:
 342                for v in vs:
 343                    if hasattr(v, "parent"):
 344                        yield k, v
 345            else:
 346                if hasattr(vs, "parent"):
 347                    yield k, vs
 348
 349    def find(self, *expression_types: t.Type[E], bfs: bool = True) -> t.Optional[E]:
 350        """
 351        Returns the first node in this tree which matches at least one of
 352        the specified types.
 353
 354        Args:
 355            expression_types: the expression type(s) to match.
 356            bfs: whether to search the AST using the BFS algorithm (DFS is used if false).
 357
 358        Returns:
 359            The node which matches the criteria or None if no such node was found.
 360        """
 361        return next(self.find_all(*expression_types, bfs=bfs), None)
 362
 363    def find_all(self, *expression_types: t.Type[E], bfs: bool = True) -> t.Iterator[E]:
 364        """
 365        Returns a generator object which visits all nodes in this tree and only
 366        yields those that match at least one of the specified expression types.
 367
 368        Args:
 369            expression_types: the expression type(s) to match.
 370            bfs: whether to search the AST using the BFS algorithm (DFS is used if false).
 371
 372        Returns:
 373            The generator object.
 374        """
 375        for expression, *_ in self.walk(bfs=bfs):
 376            if isinstance(expression, expression_types):
 377                yield expression
 378
 379    def find_ancestor(self, *expression_types: t.Type[E]) -> t.Optional[E]:
 380        """
 381        Returns a nearest parent matching expression_types.
 382
 383        Args:
 384            expression_types: the expression type(s) to match.
 385
 386        Returns:
 387            The parent node.
 388        """
 389        ancestor = self.parent
 390        while ancestor and not isinstance(ancestor, expression_types):
 391            ancestor = ancestor.parent
 392        return t.cast(E, ancestor)
 393
 394    @property
 395    def parent_select(self) -> t.Optional[Select]:
 396        """
 397        Returns the parent select statement.
 398        """
 399        return self.find_ancestor(Select)
 400
 401    @property
 402    def same_parent(self) -> bool:
 403        """Returns if the parent is the same class as itself."""
 404        return type(self.parent) is self.__class__
 405
 406    def root(self) -> Expression:
 407        """
 408        Returns the root expression of this tree.
 409        """
 410        expression = self
 411        while expression.parent:
 412            expression = expression.parent
 413        return expression
 414
 415    def walk(self, bfs=True, prune=None):
 416        """
 417        Returns a generator object which visits all nodes in this tree.
 418
 419        Args:
 420            bfs (bool): if set to True the BFS traversal order will be applied,
 421                otherwise the DFS traversal will be used instead.
 422            prune ((node, parent, arg_key) -> bool): callable that returns True if
 423                the generator should stop traversing this branch of the tree.
 424
 425        Returns:
 426            the generator object.
 427        """
 428        if bfs:
 429            yield from self.bfs(prune=prune)
 430        else:
 431            yield from self.dfs(prune=prune)
 432
 433    def dfs(self, parent=None, key=None, prune=None):
 434        """
 435        Returns a generator object which visits all nodes in this tree in
 436        the DFS (Depth-first) order.
 437
 438        Returns:
 439            The generator object.
 440        """
 441        parent = parent or self.parent
 442        yield self, parent, key
 443        if prune and prune(self, parent, key):
 444            return
 445
 446        for k, v in self.iter_expressions():
 447            yield from v.dfs(self, k, prune)
 448
 449    def bfs(self, prune=None):
 450        """
 451        Returns a generator object which visits all nodes in this tree in
 452        the BFS (Breadth-first) order.
 453
 454        Returns:
 455            The generator object.
 456        """
 457        queue = deque([(self, self.parent, None)])
 458
 459        while queue:
 460            item, parent, key = queue.popleft()
 461
 462            yield item, parent, key
 463            if prune and prune(item, parent, key):
 464                continue
 465
 466            for k, v in item.iter_expressions():
 467                queue.append((v, item, k))
 468
 469    def unnest(self):
 470        """
 471        Returns the first non parenthesis child or self.
 472        """
 473        expression = self
 474        while type(expression) is Paren:
 475            expression = expression.this
 476        return expression
 477
 478    def unalias(self):
 479        """
 480        Returns the inner expression if this is an Alias.
 481        """
 482        if isinstance(self, Alias):
 483            return self.this
 484        return self
 485
 486    def unnest_operands(self):
 487        """
 488        Returns unnested operands as a tuple.
 489        """
 490        return tuple(arg.unnest() for _, arg in self.iter_expressions())
 491
 492    def flatten(self, unnest=True):
 493        """
 494        Returns a generator which yields child nodes whose parents are the same class.
 495
 496        A AND B AND C -> [A, B, C]
 497        """
 498        for node, _, _ in self.dfs(prune=lambda n, p, *_: p and not type(n) is self.__class__):
 499            if not type(node) is self.__class__:
 500                yield node.unnest() if unnest and not isinstance(node, Subquery) else node
 501
 502    def __str__(self) -> str:
 503        return self.sql()
 504
 505    def __repr__(self) -> str:
 506        return _to_s(self)
 507
 508    def to_s(self) -> str:
 509        """
 510        Same as __repr__, but includes additional information which can be useful
 511        for debugging, like empty or missing args and the AST nodes' object IDs.
 512        """
 513        return _to_s(self, verbose=True)
 514
 515    def sql(self, dialect: DialectType = None, **opts) -> str:
 516        """
 517        Returns SQL string representation of this tree.
 518
 519        Args:
 520            dialect: the dialect of the output SQL string (eg. "spark", "hive", "presto", "mysql").
 521            opts: other `sqlglot.generator.Generator` options.
 522
 523        Returns:
 524            The SQL string.
 525        """
 526        from sqlglot.dialects import Dialect
 527
 528        return Dialect.get_or_raise(dialect).generate(self, **opts)
 529
 530    def transform(self, fun, *args, copy=True, **kwargs):
 531        """
 532        Recursively visits all tree nodes (excluding already transformed ones)
 533        and applies the given transformation function to each node.
 534
 535        Args:
 536            fun (function): a function which takes a node as an argument and returns a
 537                new transformed node or the same node without modifications. If the function
 538                returns None, then the corresponding node will be removed from the syntax tree.
 539            copy (bool): if set to True a new tree instance is constructed, otherwise the tree is
 540                modified in place.
 541
 542        Returns:
 543            The transformed tree.
 544        """
 545        node = self.copy() if copy else self
 546        new_node = fun(node, *args, **kwargs)
 547
 548        if new_node is None or not isinstance(new_node, Expression):
 549            return new_node
 550        if new_node is not node:
 551            new_node.parent = node.parent
 552            return new_node
 553
 554        replace_children(new_node, lambda child: child.transform(fun, *args, copy=False, **kwargs))
 555        return new_node
 556
 557    @t.overload
 558    def replace(self, expression: E) -> E:
 559        ...
 560
 561    @t.overload
 562    def replace(self, expression: None) -> None:
 563        ...
 564
 565    def replace(self, expression):
 566        """
 567        Swap out this expression with a new expression.
 568
 569        For example::
 570
 571            >>> tree = Select().select("x").from_("tbl")
 572            >>> tree.find(Column).replace(column("y"))
 573            Column(
 574              this=Identifier(this=y, quoted=False))
 575            >>> tree.sql()
 576            'SELECT y FROM tbl'
 577
 578        Args:
 579            expression: new node
 580
 581        Returns:
 582            The new expression or expressions.
 583        """
 584        if not self.parent:
 585            return expression
 586
 587        parent = self.parent
 588        self.parent = None
 589
 590        replace_children(parent, lambda child: expression if child is self else child)
 591        return expression
 592
 593    def pop(self: E) -> E:
 594        """
 595        Remove this expression from its AST.
 596
 597        Returns:
 598            The popped expression.
 599        """
 600        self.replace(None)
 601        return self
 602
 603    def assert_is(self, type_: t.Type[E]) -> E:
 604        """
 605        Assert that this `Expression` is an instance of `type_`.
 606
 607        If it is NOT an instance of `type_`, this raises an assertion error.
 608        Otherwise, this returns this expression.
 609
 610        Examples:
 611            This is useful for type security in chained expressions:
 612
 613            >>> import sqlglot
 614            >>> sqlglot.parse_one("SELECT x from y").assert_is(Select).select("z").sql()
 615            'SELECT x, z FROM y'
 616        """
 617        assert isinstance(self, type_)
 618        return self
 619
 620    def error_messages(self, args: t.Optional[t.Sequence] = None) -> t.List[str]:
 621        """
 622        Checks if this expression is valid (e.g. all mandatory args are set).
 623
 624        Args:
 625            args: a sequence of values that were used to instantiate a Func expression. This is used
 626                to check that the provided arguments don't exceed the function argument limit.
 627
 628        Returns:
 629            A list of error messages for all possible errors that were found.
 630        """
 631        errors: t.List[str] = []
 632
 633        for k in self.args:
 634            if k not in self.arg_types:
 635                errors.append(f"Unexpected keyword: '{k}' for {self.__class__}")
 636        for k, mandatory in self.arg_types.items():
 637            v = self.args.get(k)
 638            if mandatory and (v is None or (isinstance(v, list) and not v)):
 639                errors.append(f"Required keyword: '{k}' missing for {self.__class__}")
 640
 641        if (
 642            args
 643            and isinstance(self, Func)
 644            and len(args) > len(self.arg_types)
 645            and not self.is_var_len_args
 646        ):
 647            errors.append(
 648                f"The number of provided arguments ({len(args)}) is greater than "
 649                f"the maximum number of supported arguments ({len(self.arg_types)})"
 650            )
 651
 652        return errors
 653
 654    def dump(self):
 655        """
 656        Dump this Expression to a JSON-serializable dict.
 657        """
 658        from sqlglot.serde import dump
 659
 660        return dump(self)
 661
 662    @classmethod
 663    def load(cls, obj):
 664        """
 665        Load a dict (as returned by `Expression.dump`) into an Expression instance.
 666        """
 667        from sqlglot.serde import load
 668
 669        return load(obj)
 670
 671    def and_(
 672        self,
 673        *expressions: t.Optional[ExpOrStr],
 674        dialect: DialectType = None,
 675        copy: bool = True,
 676        **opts,
 677    ) -> Condition:
 678        """
 679        AND this condition with one or multiple expressions.
 680
 681        Example:
 682            >>> condition("x=1").and_("y=1").sql()
 683            'x = 1 AND y = 1'
 684
 685        Args:
 686            *expressions: the SQL code strings to parse.
 687                If an `Expression` instance is passed, it will be used as-is.
 688            dialect: the dialect used to parse the input expression.
 689            copy: whether or not to copy the involved expressions (only applies to Expressions).
 690            opts: other options to use to parse the input expressions.
 691
 692        Returns:
 693            The new And condition.
 694        """
 695        return and_(self, *expressions, dialect=dialect, copy=copy, **opts)
 696
 697    def or_(
 698        self,
 699        *expressions: t.Optional[ExpOrStr],
 700        dialect: DialectType = None,
 701        copy: bool = True,
 702        **opts,
 703    ) -> Condition:
 704        """
 705        OR this condition with one or multiple expressions.
 706
 707        Example:
 708            >>> condition("x=1").or_("y=1").sql()
 709            'x = 1 OR y = 1'
 710
 711        Args:
 712            *expressions: the SQL code strings to parse.
 713                If an `Expression` instance is passed, it will be used as-is.
 714            dialect: the dialect used to parse the input expression.
 715            copy: whether or not to copy the involved expressions (only applies to Expressions).
 716            opts: other options to use to parse the input expressions.
 717
 718        Returns:
 719            The new Or condition.
 720        """
 721        return or_(self, *expressions, dialect=dialect, copy=copy, **opts)
 722
 723    def not_(self, copy: bool = True):
 724        """
 725        Wrap this condition with NOT.
 726
 727        Example:
 728            >>> condition("x=1").not_().sql()
 729            'NOT x = 1'
 730
 731        Args:
 732            copy: whether or not to copy this object.
 733
 734        Returns:
 735            The new Not instance.
 736        """
 737        return not_(self, copy=copy)
 738
 739    def as_(
 740        self,
 741        alias: str | Identifier,
 742        quoted: t.Optional[bool] = None,
 743        dialect: DialectType = None,
 744        copy: bool = True,
 745        **opts,
 746    ) -> Alias:
 747        return alias_(self, alias, quoted=quoted, dialect=dialect, copy=copy, **opts)
 748
 749    def _binop(self, klass: t.Type[E], other: t.Any, reverse: bool = False) -> E:
 750        this = self.copy()
 751        other = convert(other, copy=True)
 752        if not isinstance(this, klass) and not isinstance(other, klass):
 753            this = _wrap(this, Binary)
 754            other = _wrap(other, Binary)
 755        if reverse:
 756            return klass(this=other, expression=this)
 757        return klass(this=this, expression=other)
 758
 759    def __getitem__(self, other: ExpOrStr | t.Tuple[ExpOrStr]) -> Bracket:
 760        return Bracket(
 761            this=self.copy(), expressions=[convert(e, copy=True) for e in ensure_list(other)]
 762        )
 763
 764    def __iter__(self) -> t.Iterator:
 765        if "expressions" in self.arg_types:
 766            return iter(self.args.get("expressions") or [])
 767        # We define this because __getitem__ converts Expression into an iterable, which is
 768        # problematic because one can hit infinite loops if they do "for x in some_expr: ..."
 769        # See: https://peps.python.org/pep-0234/
 770        raise TypeError(f"'{self.__class__.__name__}' object is not iterable")
 771
 772    def isin(
 773        self,
 774        *expressions: t.Any,
 775        query: t.Optional[ExpOrStr] = None,
 776        unnest: t.Optional[ExpOrStr] | t.Collection[ExpOrStr] = None,
 777        copy: bool = True,
 778        **opts,
 779    ) -> In:
 780        return In(
 781            this=maybe_copy(self, copy),
 782            expressions=[convert(e, copy=copy) for e in expressions],
 783            query=maybe_parse(query, copy=copy, **opts) if query else None,
 784            unnest=Unnest(
 785                expressions=[
 786                    maybe_parse(t.cast(ExpOrStr, e), copy=copy, **opts) for e in ensure_list(unnest)
 787                ]
 788            )
 789            if unnest
 790            else None,
 791        )
 792
 793    def between(self, low: t.Any, high: t.Any, copy: bool = True, **opts) -> Between:
 794        return Between(
 795            this=maybe_copy(self, copy),
 796            low=convert(low, copy=copy, **opts),
 797            high=convert(high, copy=copy, **opts),
 798        )
 799
 800    def is_(self, other: ExpOrStr) -> Is:
 801        return self._binop(Is, other)
 802
 803    def like(self, other: ExpOrStr) -> Like:
 804        return self._binop(Like, other)
 805
 806    def ilike(self, other: ExpOrStr) -> ILike:
 807        return self._binop(ILike, other)
 808
 809    def eq(self, other: t.Any) -> EQ:
 810        return self._binop(EQ, other)
 811
 812    def neq(self, other: t.Any) -> NEQ:
 813        return self._binop(NEQ, other)
 814
 815    def rlike(self, other: ExpOrStr) -> RegexpLike:
 816        return self._binop(RegexpLike, other)
 817
 818    def div(self, other: ExpOrStr, typed: bool = False, safe: bool = False) -> Div:
 819        div = self._binop(Div, other)
 820        div.args["typed"] = typed
 821        div.args["safe"] = safe
 822        return div
 823
 824    def desc(self, nulls_first: bool = False) -> Ordered:
 825        return Ordered(this=self.copy(), desc=True, nulls_first=nulls_first)
 826
 827    def __lt__(self, other: t.Any) -> LT:
 828        return self._binop(LT, other)
 829
 830    def __le__(self, other: t.Any) -> LTE:
 831        return self._binop(LTE, other)
 832
 833    def __gt__(self, other: t.Any) -> GT:
 834        return self._binop(GT, other)
 835
 836    def __ge__(self, other: t.Any) -> GTE:
 837        return self._binop(GTE, other)
 838
 839    def __add__(self, other: t.Any) -> Add:
 840        return self._binop(Add, other)
 841
 842    def __radd__(self, other: t.Any) -> Add:
 843        return self._binop(Add, other, reverse=True)
 844
 845    def __sub__(self, other: t.Any) -> Sub:
 846        return self._binop(Sub, other)
 847
 848    def __rsub__(self, other: t.Any) -> Sub:
 849        return self._binop(Sub, other, reverse=True)
 850
 851    def __mul__(self, other: t.Any) -> Mul:
 852        return self._binop(Mul, other)
 853
 854    def __rmul__(self, other: t.Any) -> Mul:
 855        return self._binop(Mul, other, reverse=True)
 856
 857    def __truediv__(self, other: t.Any) -> Div:
 858        return self._binop(Div, other)
 859
 860    def __rtruediv__(self, other: t.Any) -> Div:
 861        return self._binop(Div, other, reverse=True)
 862
 863    def __floordiv__(self, other: t.Any) -> IntDiv:
 864        return self._binop(IntDiv, other)
 865
 866    def __rfloordiv__(self, other: t.Any) -> IntDiv:
 867        return self._binop(IntDiv, other, reverse=True)
 868
 869    def __mod__(self, other: t.Any) -> Mod:
 870        return self._binop(Mod, other)
 871
 872    def __rmod__(self, other: t.Any) -> Mod:
 873        return self._binop(Mod, other, reverse=True)
 874
 875    def __pow__(self, other: t.Any) -> Pow:
 876        return self._binop(Pow, other)
 877
 878    def __rpow__(self, other: t.Any) -> Pow:
 879        return self._binop(Pow, other, reverse=True)
 880
 881    def __and__(self, other: t.Any) -> And:
 882        return self._binop(And, other)
 883
 884    def __rand__(self, other: t.Any) -> And:
 885        return self._binop(And, other, reverse=True)
 886
 887    def __or__(self, other: t.Any) -> Or:
 888        return self._binop(Or, other)
 889
 890    def __ror__(self, other: t.Any) -> Or:
 891        return self._binop(Or, other, reverse=True)
 892
 893    def __neg__(self) -> Neg:
 894        return Neg(this=_wrap(self.copy(), Binary))
 895
 896    def __invert__(self) -> Not:
 897        return not_(self.copy())
 898
 899
 900IntoType = t.Union[
 901    str,
 902    t.Type[Expression],
 903    t.Collection[t.Union[str, t.Type[Expression]]],
 904]
 905ExpOrStr = t.Union[str, Expression]
 906
 907
 908class Condition(Expression):
 909    """Logical conditions like x AND y, or simply x"""
 910
 911
 912class Predicate(Condition):
 913    """Relationships like x = y, x > 1, x >= y."""
 914
 915
 916class DerivedTable(Expression):
 917    @property
 918    def selects(self) -> t.List[Expression]:
 919        return self.this.selects if isinstance(self.this, Subqueryable) else []
 920
 921    @property
 922    def named_selects(self) -> t.List[str]:
 923        return [select.output_name for select in self.selects]
 924
 925
 926class Unionable(Expression):
 927    def union(
 928        self, expression: ExpOrStr, distinct: bool = True, dialect: DialectType = None, **opts
 929    ) -> Unionable:
 930        """
 931        Builds a UNION expression.
 932
 933        Example:
 934            >>> import sqlglot
 935            >>> sqlglot.parse_one("SELECT * FROM foo").union("SELECT * FROM bla").sql()
 936            'SELECT * FROM foo UNION SELECT * FROM bla'
 937
 938        Args:
 939            expression: the SQL code string.
 940                If an `Expression` instance is passed, it will be used as-is.
 941            distinct: set the DISTINCT flag if and only if this is true.
 942            dialect: the dialect used to parse the input expression.
 943            opts: other options to use to parse the input expressions.
 944
 945        Returns:
 946            The new Union expression.
 947        """
 948        return union(left=self, right=expression, distinct=distinct, dialect=dialect, **opts)
 949
 950    def intersect(
 951        self, expression: ExpOrStr, distinct: bool = True, dialect: DialectType = None, **opts
 952    ) -> Unionable:
 953        """
 954        Builds an INTERSECT expression.
 955
 956        Example:
 957            >>> import sqlglot
 958            >>> sqlglot.parse_one("SELECT * FROM foo").intersect("SELECT * FROM bla").sql()
 959            'SELECT * FROM foo INTERSECT SELECT * FROM bla'
 960
 961        Args:
 962            expression: the SQL code string.
 963                If an `Expression` instance is passed, it will be used as-is.
 964            distinct: set the DISTINCT flag if and only if this is true.
 965            dialect: the dialect used to parse the input expression.
 966            opts: other options to use to parse the input expressions.
 967
 968        Returns:
 969            The new Intersect expression.
 970        """
 971        return intersect(left=self, right=expression, distinct=distinct, dialect=dialect, **opts)
 972
 973    def except_(
 974        self, expression: ExpOrStr, distinct: bool = True, dialect: DialectType = None, **opts
 975    ) -> Unionable:
 976        """
 977        Builds an EXCEPT expression.
 978
 979        Example:
 980            >>> import sqlglot
 981            >>> sqlglot.parse_one("SELECT * FROM foo").except_("SELECT * FROM bla").sql()
 982            'SELECT * FROM foo EXCEPT SELECT * FROM bla'
 983
 984        Args:
 985            expression: the SQL code string.
 986                If an `Expression` instance is passed, it will be used as-is.
 987            distinct: set the DISTINCT flag if and only if this is true.
 988            dialect: the dialect used to parse the input expression.
 989            opts: other options to use to parse the input expressions.
 990
 991        Returns:
 992            The new Except expression.
 993        """
 994        return except_(left=self, right=expression, distinct=distinct, dialect=dialect, **opts)
 995
 996
 997class UDTF(DerivedTable, Unionable):
 998    @property
 999    def selects(self) -> t.List[Expression]:
1000        alias = self.args.get("alias")
1001        return alias.columns if alias else []
1002
1003
1004class Cache(Expression):
1005    arg_types = {
1006        "this": True,
1007        "lazy": False,
1008        "options": False,
1009        "expression": False,
1010    }
1011
1012
1013class Uncache(Expression):
1014    arg_types = {"this": True, "exists": False}
1015
1016
1017class Refresh(Expression):
1018    pass
1019
1020
1021class DDL(Expression):
1022    @property
1023    def ctes(self):
1024        with_ = self.args.get("with")
1025        if not with_:
1026            return []
1027        return with_.expressions
1028
1029    @property
1030    def named_selects(self) -> t.List[str]:
1031        if isinstance(self.expression, Subqueryable):
1032            return self.expression.named_selects
1033        return []
1034
1035    @property
1036    def selects(self) -> t.List[Expression]:
1037        if isinstance(self.expression, Subqueryable):
1038            return self.expression.selects
1039        return []
1040
1041
1042class DML(Expression):
1043    def returning(
1044        self,
1045        expression: ExpOrStr,
1046        dialect: DialectType = None,
1047        copy: bool = True,
1048        **opts,
1049    ) -> DML:
1050        """
1051        Set the RETURNING expression. Not supported by all dialects.
1052
1053        Example:
1054            >>> delete("tbl").returning("*", dialect="postgres").sql()
1055            'DELETE FROM tbl RETURNING *'
1056
1057        Args:
1058            expression: the SQL code strings to parse.
1059                If an `Expression` instance is passed, it will be used as-is.
1060            dialect: the dialect used to parse the input expressions.
1061            copy: if `False`, modify this expression instance in-place.
1062            opts: other options to use to parse the input expressions.
1063
1064        Returns:
1065            Delete: the modified expression.
1066        """
1067        return _apply_builder(
1068            expression=expression,
1069            instance=self,
1070            arg="returning",
1071            prefix="RETURNING",
1072            dialect=dialect,
1073            copy=copy,
1074            into=Returning,
1075            **opts,
1076        )
1077
1078
1079class Create(DDL):
1080    arg_types = {
1081        "with": False,
1082        "this": True,
1083        "kind": True,
1084        "expression": False,
1085        "exists": False,
1086        "properties": False,
1087        "replace": False,
1088        "unique": False,
1089        "indexes": False,
1090        "no_schema_binding": False,
1091        "begin": False,
1092        "end": False,
1093        "clone": False,
1094    }
1095
1096
1097# https://docs.snowflake.com/en/sql-reference/sql/create-clone
1098# https://cloud.google.com/bigquery/docs/reference/standard-sql/data-definition-language#create_table_clone_statement
1099# https://cloud.google.com/bigquery/docs/reference/standard-sql/data-definition-language#create_table_copy
1100class Clone(Expression):
1101    arg_types = {"this": True, "shallow": False, "copy": False}
1102
1103
1104class Describe(Expression):
1105    arg_types = {"this": True, "extended": False, "kind": False, "expressions": False}
1106
1107
1108class Kill(Expression):
1109    arg_types = {"this": True, "kind": False}
1110
1111
1112class Pragma(Expression):
1113    pass
1114
1115
1116class Set(Expression):
1117    arg_types = {"expressions": False, "unset": False, "tag": False}
1118
1119
1120class Heredoc(Expression):
1121    arg_types = {"this": True, "tag": False}
1122
1123
1124class SetItem(Expression):
1125    arg_types = {
1126        "this": False,
1127        "expressions": False,
1128        "kind": False,
1129        "collate": False,  # MySQL SET NAMES statement
1130        "global": False,
1131    }
1132
1133
1134class Show(Expression):
1135    arg_types = {
1136        "this": True,
1137        "target": False,
1138        "offset": False,
1139        "limit": False,
1140        "like": False,
1141        "where": False,
1142        "db": False,
1143        "scope": False,
1144        "scope_kind": False,
1145        "full": False,
1146        "mutex": False,
1147        "query": False,
1148        "channel": False,
1149        "global": False,
1150        "log": False,
1151        "position": False,
1152        "types": False,
1153    }
1154
1155
1156class UserDefinedFunction(Expression):
1157    arg_types = {"this": True, "expressions": False, "wrapped": False}
1158
1159
1160class CharacterSet(Expression):
1161    arg_types = {"this": True, "default": False}
1162
1163
1164class With(Expression):
1165    arg_types = {"expressions": True, "recursive": False}
1166
1167    @property
1168    def recursive(self) -> bool:
1169        return bool(self.args.get("recursive"))
1170
1171
1172class WithinGroup(Expression):
1173    arg_types = {"this": True, "expression": False}
1174
1175
1176# clickhouse supports scalar ctes
1177# https://clickhouse.com/docs/en/sql-reference/statements/select/with
1178class CTE(DerivedTable):
1179    arg_types = {"this": True, "alias": True, "scalar": False}
1180
1181
1182class TableAlias(Expression):
1183    arg_types = {"this": False, "columns": False}
1184
1185    @property
1186    def columns(self):
1187        return self.args.get("columns") or []
1188
1189
1190class BitString(Condition):
1191    pass
1192
1193
1194class HexString(Condition):
1195    pass
1196
1197
1198class ByteString(Condition):
1199    pass
1200
1201
1202class RawString(Condition):
1203    pass
1204
1205
1206class UnicodeString(Condition):
1207    arg_types = {"this": True, "escape": False}
1208
1209
1210class Column(Condition):
1211    arg_types = {"this": True, "table": False, "db": False, "catalog": False, "join_mark": False}
1212
1213    @property
1214    def table(self) -> str:
1215        return self.text("table")
1216
1217    @property
1218    def db(self) -> str:
1219        return self.text("db")
1220
1221    @property
1222    def catalog(self) -> str:
1223        return self.text("catalog")
1224
1225    @property
1226    def output_name(self) -> str:
1227        return self.name
1228
1229    @property
1230    def parts(self) -> t.List[Identifier]:
1231        """Return the parts of a column in order catalog, db, table, name."""
1232        return [
1233            t.cast(Identifier, self.args[part])
1234            for part in ("catalog", "db", "table", "this")
1235            if self.args.get(part)
1236        ]
1237
1238    def to_dot(self) -> Dot | Identifier:
1239        """Converts the column into a dot expression."""
1240        parts = self.parts
1241        parent = self.parent
1242
1243        while parent:
1244            if isinstance(parent, Dot):
1245                parts.append(parent.expression)
1246            parent = parent.parent
1247
1248        return Dot.build(deepcopy(parts)) if len(parts) > 1 else parts[0]
1249
1250
1251class ColumnPosition(Expression):
1252    arg_types = {"this": False, "position": True}
1253
1254
1255class ColumnDef(Expression):
1256    arg_types = {
1257        "this": True,
1258        "kind": False,
1259        "constraints": False,
1260        "exists": False,
1261        "position": False,
1262    }
1263
1264    @property
1265    def constraints(self) -> t.List[ColumnConstraint]:
1266        return self.args.get("constraints") or []
1267
1268
1269class AlterColumn(Expression):
1270    arg_types = {
1271        "this": True,
1272        "dtype": False,
1273        "collate": False,
1274        "using": False,
1275        "default": False,
1276        "drop": False,
1277    }
1278
1279
1280class RenameTable(Expression):
1281    pass
1282
1283
1284class SwapTable(Expression):
1285    pass
1286
1287
1288class Comment(Expression):
1289    arg_types = {"this": True, "kind": True, "expression": True, "exists": False}
1290
1291
1292class Comprehension(Expression):
1293    arg_types = {"this": True, "expression": True, "iterator": True, "condition": False}
1294
1295
1296# https://clickhouse.com/docs/en/engines/table-engines/mergetree-family/mergetree#mergetree-table-ttl
1297class MergeTreeTTLAction(Expression):
1298    arg_types = {
1299        "this": True,
1300        "delete": False,
1301        "recompress": False,
1302        "to_disk": False,
1303        "to_volume": False,
1304    }
1305
1306
1307# https://clickhouse.com/docs/en/engines/table-engines/mergetree-family/mergetree#mergetree-table-ttl
1308class MergeTreeTTL(Expression):
1309    arg_types = {
1310        "expressions": True,
1311        "where": False,
1312        "group": False,
1313        "aggregates": False,
1314    }
1315
1316
1317# https://dev.mysql.com/doc/refman/8.0/en/create-table.html
1318class IndexConstraintOption(Expression):
1319    arg_types = {
1320        "key_block_size": False,
1321        "using": False,
1322        "parser": False,
1323        "comment": False,
1324        "visible": False,
1325        "engine_attr": False,
1326        "secondary_engine_attr": False,
1327    }
1328
1329
1330class ColumnConstraint(Expression):
1331    arg_types = {"this": False, "kind": True}
1332
1333    @property
1334    def kind(self) -> ColumnConstraintKind:
1335        return self.args["kind"]
1336
1337
1338class ColumnConstraintKind(Expression):
1339    pass
1340
1341
1342class AutoIncrementColumnConstraint(ColumnConstraintKind):
1343    pass
1344
1345
1346class PeriodForSystemTimeConstraint(ColumnConstraintKind):
1347    arg_types = {"this": True, "expression": True}
1348
1349
1350class CaseSpecificColumnConstraint(ColumnConstraintKind):
1351    arg_types = {"not_": True}
1352
1353
1354class CharacterSetColumnConstraint(ColumnConstraintKind):
1355    arg_types = {"this": True}
1356
1357
1358class CheckColumnConstraint(ColumnConstraintKind):
1359    pass
1360
1361
1362class ClusteredColumnConstraint(ColumnConstraintKind):
1363    pass
1364
1365
1366class CollateColumnConstraint(ColumnConstraintKind):
1367    pass
1368
1369
1370class CommentColumnConstraint(ColumnConstraintKind):
1371    pass
1372
1373
1374class CompressColumnConstraint(ColumnConstraintKind):
1375    pass
1376
1377
1378class DateFormatColumnConstraint(ColumnConstraintKind):
1379    arg_types = {"this": True}
1380
1381
1382class DefaultColumnConstraint(ColumnConstraintKind):
1383    pass
1384
1385
1386class EncodeColumnConstraint(ColumnConstraintKind):
1387    pass
1388
1389
1390class GeneratedAsIdentityColumnConstraint(ColumnConstraintKind):
1391    # this: True -> ALWAYS, this: False -> BY DEFAULT
1392    arg_types = {
1393        "this": False,
1394        "expression": False,
1395        "on_null": False,
1396        "start": False,
1397        "increment": False,
1398        "minvalue": False,
1399        "maxvalue": False,
1400        "cycle": False,
1401    }
1402
1403
1404class GeneratedAsRowColumnConstraint(ColumnConstraintKind):
1405    arg_types = {"start": True, "hidden": False}
1406
1407
1408# https://dev.mysql.com/doc/refman/8.0/en/create-table.html
1409class IndexColumnConstraint(ColumnConstraintKind):
1410    arg_types = {
1411        "this": False,
1412        "schema": True,
1413        "kind": False,
1414        "index_type": False,
1415        "options": False,
1416    }
1417
1418
1419class InlineLengthColumnConstraint(ColumnConstraintKind):
1420    pass
1421
1422
1423class NonClusteredColumnConstraint(ColumnConstraintKind):
1424    pass
1425
1426
1427class NotForReplicationColumnConstraint(ColumnConstraintKind):
1428    arg_types = {}
1429
1430
1431class NotNullColumnConstraint(ColumnConstraintKind):
1432    arg_types = {"allow_null": False}
1433
1434
1435# https://dev.mysql.com/doc/refman/5.7/en/timestamp-initialization.html
1436class OnUpdateColumnConstraint(ColumnConstraintKind):
1437    pass
1438
1439
1440# https://docs.snowflake.com/en/sql-reference/sql/create-external-table#optional-parameters
1441class TransformColumnConstraint(ColumnConstraintKind):
1442    pass
1443
1444
1445class PrimaryKeyColumnConstraint(ColumnConstraintKind):
1446    arg_types = {"desc": False}
1447
1448
1449class TitleColumnConstraint(ColumnConstraintKind):
1450    pass
1451
1452
1453class UniqueColumnConstraint(ColumnConstraintKind):
1454    arg_types = {"this": False, "index_type": False}
1455
1456
1457class UppercaseColumnConstraint(ColumnConstraintKind):
1458    arg_types: t.Dict[str, t.Any] = {}
1459
1460
1461class PathColumnConstraint(ColumnConstraintKind):
1462    pass
1463
1464
1465# computed column expression
1466# https://learn.microsoft.com/en-us/sql/t-sql/statements/create-table-transact-sql?view=sql-server-ver16
1467class ComputedColumnConstraint(ColumnConstraintKind):
1468    arg_types = {"this": True, "persisted": False, "not_null": False}
1469
1470
1471class Constraint(Expression):
1472    arg_types = {"this": True, "expressions": True}
1473
1474
1475class Delete(DML):
1476    arg_types = {
1477        "with": False,
1478        "this": False,
1479        "using": False,
1480        "where": False,
1481        "returning": False,
1482        "limit": False,
1483        "tables": False,  # Multiple-Table Syntax (MySQL)
1484    }
1485
1486    def delete(
1487        self,
1488        table: ExpOrStr,
1489        dialect: DialectType = None,
1490        copy: bool = True,
1491        **opts,
1492    ) -> Delete:
1493        """
1494        Create a DELETE expression or replace the table on an existing DELETE expression.
1495
1496        Example:
1497            >>> delete("tbl").sql()
1498            'DELETE FROM tbl'
1499
1500        Args:
1501            table: the table from which to delete.
1502            dialect: the dialect used to parse the input expression.
1503            copy: if `False`, modify this expression instance in-place.
1504            opts: other options to use to parse the input expressions.
1505
1506        Returns:
1507            Delete: the modified expression.
1508        """
1509        return _apply_builder(
1510            expression=table,
1511            instance=self,
1512            arg="this",
1513            dialect=dialect,
1514            into=Table,
1515            copy=copy,
1516            **opts,
1517        )
1518
1519    def where(
1520        self,
1521        *expressions: t.Optional[ExpOrStr],
1522        append: bool = True,
1523        dialect: DialectType = None,
1524        copy: bool = True,
1525        **opts,
1526    ) -> Delete:
1527        """
1528        Append to or set the WHERE expressions.
1529
1530        Example:
1531            >>> delete("tbl").where("x = 'a' OR x < 'b'").sql()
1532            "DELETE FROM tbl WHERE x = 'a' OR x < 'b'"
1533
1534        Args:
1535            *expressions: the SQL code strings to parse.
1536                If an `Expression` instance is passed, it will be used as-is.
1537                Multiple expressions are combined with an AND operator.
1538            append: if `True`, AND the new expressions to any existing expression.
1539                Otherwise, this resets the expression.
1540            dialect: the dialect used to parse the input expressions.
1541            copy: if `False`, modify this expression instance in-place.
1542            opts: other options to use to parse the input expressions.
1543
1544        Returns:
1545            Delete: the modified expression.
1546        """
1547        return _apply_conjunction_builder(
1548            *expressions,
1549            instance=self,
1550            arg="where",
1551            append=append,
1552            into=Where,
1553            dialect=dialect,
1554            copy=copy,
1555            **opts,
1556        )
1557
1558
1559class Drop(Expression):
1560    arg_types = {
1561        "this": False,
1562        "kind": False,
1563        "exists": False,
1564        "temporary": False,
1565        "materialized": False,
1566        "cascade": False,
1567        "constraints": False,
1568        "purge": False,
1569    }
1570
1571
1572class Filter(Expression):
1573    arg_types = {"this": True, "expression": True}
1574
1575
1576class Check(Expression):
1577    pass
1578
1579
1580# https://docs.snowflake.com/en/sql-reference/constructs/connect-by
1581class Connect(Expression):
1582    arg_types = {"start": False, "connect": True}
1583
1584
1585class Prior(Expression):
1586    pass
1587
1588
1589class Directory(Expression):
1590    # https://spark.apache.org/docs/3.0.0-preview/sql-ref-syntax-dml-insert-overwrite-directory-hive.html
1591    arg_types = {"this": True, "local": False, "row_format": False}
1592
1593
1594class ForeignKey(Expression):
1595    arg_types = {
1596        "expressions": True,
1597        "reference": False,
1598        "delete": False,
1599        "update": False,
1600    }
1601
1602
1603class ColumnPrefix(Expression):
1604    arg_types = {"this": True, "expression": True}
1605
1606
1607class PrimaryKey(Expression):
1608    arg_types = {"expressions": True, "options": False}
1609
1610
1611# https://www.postgresql.org/docs/9.1/sql-selectinto.html
1612# https://docs.aws.amazon.com/redshift/latest/dg/r_SELECT_INTO.html#r_SELECT_INTO-examples
1613class Into(Expression):
1614    arg_types = {"this": True, "temporary": False, "unlogged": False}
1615
1616
1617class From(Expression):
1618    @property
1619    def name(self) -> str:
1620        return self.this.name
1621
1622    @property
1623    def alias_or_name(self) -> str:
1624        return self.this.alias_or_name
1625
1626
1627class Having(Expression):
1628    pass
1629
1630
1631class Hint(Expression):
1632    arg_types = {"expressions": True}
1633
1634
1635class JoinHint(Expression):
1636    arg_types = {"this": True, "expressions": True}
1637
1638
1639class Identifier(Expression):
1640    arg_types = {"this": True, "quoted": False, "global": False, "temporary": False}
1641
1642    @property
1643    def quoted(self) -> bool:
1644        return bool(self.args.get("quoted"))
1645
1646    @property
1647    def hashable_args(self) -> t.Any:
1648        return (self.this, self.quoted)
1649
1650    @property
1651    def output_name(self) -> str:
1652        return self.name
1653
1654
1655# https://www.postgresql.org/docs/current/indexes-opclass.html
1656class Opclass(Expression):
1657    arg_types = {"this": True, "expression": True}
1658
1659
1660class Index(Expression):
1661    arg_types = {
1662        "this": False,
1663        "table": False,
1664        "using": False,
1665        "where": False,
1666        "columns": False,
1667        "unique": False,
1668        "primary": False,
1669        "amp": False,  # teradata
1670        "partition_by": False,  # teradata
1671        "where": False,  # postgres partial indexes
1672    }
1673
1674
1675class Insert(DDL, DML):
1676    arg_types = {
1677        "with": False,
1678        "this": True,
1679        "expression": False,
1680        "conflict": False,
1681        "returning": False,
1682        "overwrite": False,
1683        "exists": False,
1684        "partition": False,
1685        "alternative": False,
1686        "where": False,
1687        "ignore": False,
1688        "by_name": False,
1689    }
1690
1691    def with_(
1692        self,
1693        alias: ExpOrStr,
1694        as_: ExpOrStr,
1695        recursive: t.Optional[bool] = None,
1696        append: bool = True,
1697        dialect: DialectType = None,
1698        copy: bool = True,
1699        **opts,
1700    ) -> Insert:
1701        """
1702        Append to or set the common table expressions.
1703
1704        Example:
1705            >>> insert("SELECT x FROM cte", "t").with_("cte", as_="SELECT * FROM tbl").sql()
1706            'WITH cte AS (SELECT * FROM tbl) INSERT INTO t SELECT x FROM cte'
1707
1708        Args:
1709            alias: the SQL code string to parse as the table name.
1710                If an `Expression` instance is passed, this is used as-is.
1711            as_: the SQL code string to parse as the table expression.
1712                If an `Expression` instance is passed, it will be used as-is.
1713            recursive: set the RECURSIVE part of the expression. Defaults to `False`.
1714            append: if `True`, add to any existing expressions.
1715                Otherwise, this resets the expressions.
1716            dialect: the dialect used to parse the input expression.
1717            copy: if `False`, modify this expression instance in-place.
1718            opts: other options to use to parse the input expressions.
1719
1720        Returns:
1721            The modified expression.
1722        """
1723        return _apply_cte_builder(
1724            self, alias, as_, recursive=recursive, append=append, dialect=dialect, copy=copy, **opts
1725        )
1726
1727
1728class OnConflict(Expression):
1729    arg_types = {
1730        "duplicate": False,
1731        "expressions": False,
1732        "nothing": False,
1733        "key": False,
1734        "constraint": False,
1735    }
1736
1737
1738class Returning(Expression):
1739    arg_types = {"expressions": True, "into": False}
1740
1741
1742# https://dev.mysql.com/doc/refman/8.0/en/charset-introducer.html
1743class Introducer(Expression):
1744    arg_types = {"this": True, "expression": True}
1745
1746
1747# national char, like n'utf8'
1748class National(Expression):
1749    pass
1750
1751
1752class LoadData(Expression):
1753    arg_types = {
1754        "this": True,
1755        "local": False,
1756        "overwrite": False,
1757        "inpath": True,
1758        "partition": False,
1759        "input_format": False,
1760        "serde": False,
1761    }
1762
1763
1764class Partition(Expression):
1765    arg_types = {"expressions": True}
1766
1767
1768class Fetch(Expression):
1769    arg_types = {
1770        "direction": False,
1771        "count": False,
1772        "percent": False,
1773        "with_ties": False,
1774    }
1775
1776
1777class Group(Expression):
1778    arg_types = {
1779        "expressions": False,
1780        "grouping_sets": False,
1781        "cube": False,
1782        "rollup": False,
1783        "totals": False,
1784        "all": False,
1785    }
1786
1787
1788class Lambda(Expression):
1789    arg_types = {"this": True, "expressions": True}
1790
1791
1792class Limit(Expression):
1793    arg_types = {"this": False, "expression": True, "offset": False}
1794
1795
1796class Literal(Condition):
1797    arg_types = {"this": True, "is_string": True}
1798
1799    @property
1800    def hashable_args(self) -> t.Any:
1801        return (self.this, self.args.get("is_string"))
1802
1803    @classmethod
1804    def number(cls, number) -> Literal:
1805        return cls(this=str(number), is_string=False)
1806
1807    @classmethod
1808    def string(cls, string) -> Literal:
1809        return cls(this=str(string), is_string=True)
1810
1811    @property
1812    def output_name(self) -> str:
1813        return self.name
1814
1815
1816class Join(Expression):
1817    arg_types = {
1818        "this": True,
1819        "on": False,
1820        "side": False,
1821        "kind": False,
1822        "using": False,
1823        "method": False,
1824        "global": False,
1825        "hint": False,
1826    }
1827
1828    @property
1829    def method(self) -> str:
1830        return self.text("method").upper()
1831
1832    @property
1833    def kind(self) -> str:
1834        return self.text("kind").upper()
1835
1836    @property
1837    def side(self) -> str:
1838        return self.text("side").upper()
1839
1840    @property
1841    def hint(self) -> str:
1842        return self.text("hint").upper()
1843
1844    @property
1845    def alias_or_name(self) -> str:
1846        return self.this.alias_or_name
1847
1848    def on(
1849        self,
1850        *expressions: t.Optional[ExpOrStr],
1851        append: bool = True,
1852        dialect: DialectType = None,
1853        copy: bool = True,
1854        **opts,
1855    ) -> Join:
1856        """
1857        Append to or set the ON expressions.
1858
1859        Example:
1860            >>> import sqlglot
1861            >>> sqlglot.parse_one("JOIN x", into=Join).on("y = 1").sql()
1862            'JOIN x ON y = 1'
1863
1864        Args:
1865            *expressions: the SQL code strings to parse.
1866                If an `Expression` instance is passed, it will be used as-is.
1867                Multiple expressions are combined with an AND operator.
1868            append: if `True`, AND the new expressions to any existing expression.
1869                Otherwise, this resets the expression.
1870            dialect: the dialect used to parse the input expressions.
1871            copy: if `False`, modify this expression instance in-place.
1872            opts: other options to use to parse the input expressions.
1873
1874        Returns:
1875            The modified Join expression.
1876        """
1877        join = _apply_conjunction_builder(
1878            *expressions,
1879            instance=self,
1880            arg="on",
1881            append=append,
1882            dialect=dialect,
1883            copy=copy,
1884            **opts,
1885        )
1886
1887        if join.kind == "CROSS":
1888            join.set("kind", None)
1889
1890        return join
1891
1892    def using(
1893        self,
1894        *expressions: t.Optional[ExpOrStr],
1895        append: bool = True,
1896        dialect: DialectType = None,
1897        copy: bool = True,
1898        **opts,
1899    ) -> Join:
1900        """
1901        Append to or set the USING expressions.
1902
1903        Example:
1904            >>> import sqlglot
1905            >>> sqlglot.parse_one("JOIN x", into=Join).using("foo", "bla").sql()
1906            'JOIN x USING (foo, bla)'
1907
1908        Args:
1909            *expressions: the SQL code strings to parse.
1910                If an `Expression` instance is passed, it will be used as-is.
1911            append: if `True`, concatenate the new expressions to the existing "using" list.
1912                Otherwise, this resets the expression.
1913            dialect: the dialect used to parse the input expressions.
1914            copy: if `False`, modify this expression instance in-place.
1915            opts: other options to use to parse the input expressions.
1916
1917        Returns:
1918            The modified Join expression.
1919        """
1920        join = _apply_list_builder(
1921            *expressions,
1922            instance=self,
1923            arg="using",
1924            append=append,
1925            dialect=dialect,
1926            copy=copy,
1927            **opts,
1928        )
1929
1930        if join.kind == "CROSS":
1931            join.set("kind", None)
1932
1933        return join
1934
1935
1936class Lateral(UDTF):
1937    arg_types = {
1938        "this": True,
1939        "view": False,
1940        "outer": False,
1941        "alias": False,
1942        "cross_apply": False,  # True -> CROSS APPLY, False -> OUTER APPLY
1943    }
1944
1945
1946class MatchRecognize(Expression):
1947    arg_types = {
1948        "partition_by": False,
1949        "order": False,
1950        "measures": False,
1951        "rows": False,
1952        "after": False,
1953        "pattern": False,
1954        "define": False,
1955        "alias": False,
1956    }
1957
1958
1959# Clickhouse FROM FINAL modifier
1960# https://clickhouse.com/docs/en/sql-reference/statements/select/from/#final-modifier
1961class Final(Expression):
1962    pass
1963
1964
1965class Offset(Expression):
1966    arg_types = {"this": False, "expression": True}
1967
1968
1969class Order(Expression):
1970    arg_types = {
1971        "this": False,
1972        "expressions": True,
1973        "interpolate": False,
1974        "siblings": False,
1975    }
1976
1977
1978# https://clickhouse.com/docs/en/sql-reference/statements/select/order-by#order-by-expr-with-fill-modifier
1979class WithFill(Expression):
1980    arg_types = {"from": False, "to": False, "step": False}
1981
1982
1983# hive specific sorts
1984# https://cwiki.apache.org/confluence/display/Hive/LanguageManual+SortBy
1985class Cluster(Order):
1986    pass
1987
1988
1989class Distribute(Order):
1990    pass
1991
1992
1993class Sort(Order):
1994    pass
1995
1996
1997class Ordered(Expression):
1998    arg_types = {"this": True, "desc": False, "nulls_first": True, "with_fill": False}
1999
2000
2001class Property(Expression):
2002    arg_types = {"this": True, "value": True}
2003
2004
2005class AlgorithmProperty(Property):
2006    arg_types = {"this": True}
2007
2008
2009class AutoIncrementProperty(Property):
2010    arg_types = {"this": True}
2011
2012
2013# https://docs.aws.amazon.com/prescriptive-guidance/latest/materialized-views-redshift/refreshing-materialized-views.html
2014class AutoRefreshProperty(Property):
2015    arg_types = {"this": True}
2016
2017
2018class BlockCompressionProperty(Property):
2019    arg_types = {"autotemp": False, "always": False, "default": True, "manual": True, "never": True}
2020
2021
2022class CharacterSetProperty(Property):
2023    arg_types = {"this": True, "default": True}
2024
2025
2026class ChecksumProperty(Property):
2027    arg_types = {"on": False, "default": False}
2028
2029
2030class CollateProperty(Property):
2031    arg_types = {"this": True, "default": False}
2032
2033
2034class CopyGrantsProperty(Property):
2035    arg_types = {}
2036
2037
2038class DataBlocksizeProperty(Property):
2039    arg_types = {
2040        "size": False,
2041        "units": False,
2042        "minimum": False,
2043        "maximum": False,
2044        "default": False,
2045    }
2046
2047
2048class DefinerProperty(Property):
2049    arg_types = {"this": True}
2050
2051
2052class DistKeyProperty(Property):
2053    arg_types = {"this": True}
2054
2055
2056class DistStyleProperty(Property):
2057    arg_types = {"this": True}
2058
2059
2060class EngineProperty(Property):
2061    arg_types = {"this": True}
2062
2063
2064class HeapProperty(Property):
2065    arg_types = {}
2066
2067
2068class ToTableProperty(Property):
2069    arg_types = {"this": True}
2070
2071
2072class ExecuteAsProperty(Property):
2073    arg_types = {"this": True}
2074
2075
2076class ExternalProperty(Property):
2077    arg_types = {"this": False}
2078
2079
2080class FallbackProperty(Property):
2081    arg_types = {"no": True, "protection": False}
2082
2083
2084class FileFormatProperty(Property):
2085    arg_types = {"this": True}
2086
2087
2088class FreespaceProperty(Property):
2089    arg_types = {"this": True, "percent": False}
2090
2091
2092class InputModelProperty(Property):
2093    arg_types = {"this": True}
2094
2095
2096class OutputModelProperty(Property):
2097    arg_types = {"this": True}
2098
2099
2100class IsolatedLoadingProperty(Property):
2101    arg_types = {
2102        "no": True,
2103        "concurrent": True,
2104        "for_all": True,
2105        "for_insert": True,
2106        "for_none": True,
2107    }
2108
2109
2110class JournalProperty(Property):
2111    arg_types = {
2112        "no": False,
2113        "dual": False,
2114        "before": False,
2115        "local": False,
2116        "after": False,
2117    }
2118
2119
2120class LanguageProperty(Property):
2121    arg_types = {"this": True}
2122
2123
2124# spark ddl
2125class ClusteredByProperty(Property):
2126    arg_types = {"expressions": True, "sorted_by": False, "buckets": True}
2127
2128
2129class DictProperty(Property):
2130    arg_types = {"this": True, "kind": True, "settings": False}
2131
2132
2133class DictSubProperty(Property):
2134    pass
2135
2136
2137class DictRange(Property):
2138    arg_types = {"this": True, "min": True, "max": True}
2139
2140
2141# Clickhouse CREATE ... ON CLUSTER modifier
2142# https://clickhouse.com/docs/en/sql-reference/distributed-ddl
2143class OnCluster(Property):
2144    arg_types = {"this": True}
2145
2146
2147class LikeProperty(Property):
2148    arg_types = {"this": True, "expressions": False}
2149
2150
2151class LocationProperty(Property):
2152    arg_types = {"this": True}
2153
2154
2155class LockingProperty(Property):
2156    arg_types = {
2157        "this": False,
2158        "kind": True,
2159        "for_or_in": False,
2160        "lock_type": True,
2161        "override": False,
2162    }
2163
2164
2165class LogProperty(Property):
2166    arg_types = {"no": True}
2167
2168
2169class MaterializedProperty(Property):
2170    arg_types = {"this": False}
2171
2172
2173class MergeBlockRatioProperty(Property):
2174    arg_types = {"this": False, "no": False, "default": False, "percent": False}
2175
2176
2177class NoPrimaryIndexProperty(Property):
2178    arg_types = {}
2179
2180
2181class OnProperty(Property):
2182    arg_types = {"this": True}
2183
2184
2185class OnCommitProperty(Property):
2186    arg_types = {"delete": False}
2187
2188
2189class PartitionedByProperty(Property):
2190    arg_types = {"this": True}
2191
2192
2193# https://www.postgresql.org/docs/current/sql-createtable.html
2194class PartitionBoundSpec(Expression):
2195    # this -> IN / MODULUS, expression -> REMAINDER, from_expressions -> FROM (...), to_expressions -> TO (...)
2196    arg_types = {
2197        "this": False,
2198        "expression": False,
2199        "from_expressions": False,
2200        "to_expressions": False,
2201    }
2202
2203
2204class PartitionedOfProperty(Property):
2205    # this -> parent_table (schema), expression -> FOR VALUES ... / DEFAULT
2206    arg_types = {"this": True, "expression": True}
2207
2208
2209class RemoteWithConnectionModelProperty(Property):
2210    arg_types = {"this": True}
2211
2212
2213class ReturnsProperty(Property):
2214    arg_types = {"this": True, "is_table": False, "table": False}
2215
2216
2217class RowFormatProperty(Property):
2218    arg_types = {"this": True}
2219
2220
2221class RowFormatDelimitedProperty(Property):
2222    # https://cwiki.apache.org/confluence/display/hive/languagemanual+dml
2223    arg_types = {
2224        "fields": False,
2225        "escaped": False,
2226        "collection_items": False,
2227        "map_keys": False,
2228        "lines": False,
2229        "null": False,
2230        "serde": False,
2231    }
2232
2233
2234class RowFormatSerdeProperty(Property):
2235    arg_types = {"this": True, "serde_properties": False}
2236
2237
2238# https://spark.apache.org/docs/3.1.2/sql-ref-syntax-qry-select-transform.html
2239class QueryTransform(Expression):
2240    arg_types = {
2241        "expressions": True,
2242        "command_script": True,
2243        "schema": False,
2244        "row_format_before": False,
2245        "record_writer": False,
2246        "row_format_after": False,
2247        "record_reader": False,
2248    }
2249
2250
2251class SampleProperty(Property):
2252    arg_types = {"this": True}
2253
2254
2255class SchemaCommentProperty(Property):
2256    arg_types = {"this": True}
2257
2258
2259class SerdeProperties(Property):
2260    arg_types = {"expressions": True}
2261
2262
2263class SetProperty(Property):
2264    arg_types = {"multi": True}
2265
2266
2267class SettingsProperty(Property):
2268    arg_types = {"expressions": True}
2269
2270
2271class SortKeyProperty(Property):
2272    arg_types = {"this": True, "compound": False}
2273
2274
2275class SqlReadWriteProperty(Property):
2276    arg_types = {"this": True}
2277
2278
2279class SqlSecurityProperty(Property):
2280    arg_types = {"definer": True}
2281
2282
2283class StabilityProperty(Property):
2284    arg_types = {"this": True}
2285
2286
2287class TemporaryProperty(Property):
2288    arg_types = {}
2289
2290
2291class TransformModelProperty(Property):
2292    arg_types = {"expressions": True}
2293
2294
2295class TransientProperty(Property):
2296    arg_types = {"this": False}
2297
2298
2299class VolatileProperty(Property):
2300    arg_types = {"this": False}
2301
2302
2303class WithDataProperty(Property):
2304    arg_types = {"no": True, "statistics": False}
2305
2306
2307class WithJournalTableProperty(Property):
2308    arg_types = {"this": True}
2309
2310
2311class WithSystemVersioningProperty(Property):
2312    # this -> history table name, expression -> data consistency check
2313    arg_types = {"this": False, "expression": False}
2314
2315
2316class Properties(Expression):
2317    arg_types = {"expressions": True}
2318
2319    NAME_TO_PROPERTY = {
2320        "ALGORITHM": AlgorithmProperty,
2321        "AUTO_INCREMENT": AutoIncrementProperty,
2322        "CHARACTER SET": CharacterSetProperty,
2323        "CLUSTERED_BY": ClusteredByProperty,
2324        "COLLATE": CollateProperty,
2325        "COMMENT": SchemaCommentProperty,
2326        "DEFINER": DefinerProperty,
2327        "DISTKEY": DistKeyProperty,
2328        "DISTSTYLE": DistStyleProperty,
2329        "ENGINE": EngineProperty,
2330        "EXECUTE AS": ExecuteAsProperty,
2331        "FORMAT": FileFormatProperty,
2332        "LANGUAGE": LanguageProperty,
2333        "LOCATION": LocationProperty,
2334        "PARTITIONED_BY": PartitionedByProperty,
2335        "RETURNS": ReturnsProperty,
2336        "ROW_FORMAT": RowFormatProperty,
2337        "SORTKEY": SortKeyProperty,
2338    }
2339
2340    PROPERTY_TO_NAME = {v: k for k, v in NAME_TO_PROPERTY.items()}
2341
2342    # CREATE property locations
2343    # Form: schema specified
2344    #   create [POST_CREATE]
2345    #     table a [POST_NAME]
2346    #     (b int) [POST_SCHEMA]
2347    #     with ([POST_WITH])
2348    #     index (b) [POST_INDEX]
2349    #
2350    # Form: alias selection
2351    #   create [POST_CREATE]
2352    #     table a [POST_NAME]
2353    #     as [POST_ALIAS] (select * from b) [POST_EXPRESSION]
2354    #     index (c) [POST_INDEX]
2355    class Location(AutoName):
2356        POST_CREATE = auto()
2357        POST_NAME = auto()
2358        POST_SCHEMA = auto()
2359        POST_WITH = auto()
2360        POST_ALIAS = auto()
2361        POST_EXPRESSION = auto()
2362        POST_INDEX = auto()
2363        UNSUPPORTED = auto()
2364
2365    @classmethod
2366    def from_dict(cls, properties_dict: t.Dict) -> Properties:
2367        expressions = []
2368        for key, value in properties_dict.items():
2369            property_cls = cls.NAME_TO_PROPERTY.get(key.upper())
2370            if property_cls:
2371                expressions.append(property_cls(this=convert(value)))
2372            else:
2373                expressions.append(Property(this=Literal.string(key), value=convert(value)))
2374
2375        return cls(expressions=expressions)
2376
2377
2378class Qualify(Expression):
2379    pass
2380
2381
2382class InputOutputFormat(Expression):
2383    arg_types = {"input_format": False, "output_format": False}
2384
2385
2386# https://www.ibm.com/docs/en/ias?topic=procedures-return-statement-in-sql
2387class Return(Expression):
2388    pass
2389
2390
2391class Reference(Expression):
2392    arg_types = {"this": True, "expressions": False, "options": False}
2393
2394
2395class Tuple(Expression):
2396    arg_types = {"expressions": False}
2397
2398    def isin(
2399        self,
2400        *expressions: t.Any,
2401        query: t.Optional[ExpOrStr] = None,
2402        unnest: t.Optional[ExpOrStr] | t.Collection[ExpOrStr] = None,
2403        copy: bool = True,
2404        **opts,
2405    ) -> In:
2406        return In(
2407            this=maybe_copy(self, copy),
2408            expressions=[convert(e, copy=copy) for e in expressions],
2409            query=maybe_parse(query, copy=copy, **opts) if query else None,
2410            unnest=Unnest(
2411                expressions=[
2412                    maybe_parse(t.cast(ExpOrStr, e), copy=copy, **opts) for e in ensure_list(unnest)
2413                ]
2414            )
2415            if unnest
2416            else None,
2417        )
2418
2419
2420class Subqueryable(Unionable):
2421    def subquery(self, alias: t.Optional[ExpOrStr] = None, copy: bool = True) -> Subquery:
2422        """
2423        Convert this expression to an aliased expression that can be used as a Subquery.
2424
2425        Example:
2426            >>> subquery = Select().select("x").from_("tbl").subquery()
2427            >>> Select().select("x").from_(subquery).sql()
2428            'SELECT x FROM (SELECT x FROM tbl)'
2429
2430        Args:
2431            alias (str | Identifier): an optional alias for the subquery
2432            copy (bool): if `False`, modify this expression instance in-place.
2433
2434        Returns:
2435            Alias: the subquery
2436        """
2437        instance = maybe_copy(self, copy)
2438        if not isinstance(alias, Expression):
2439            alias = TableAlias(this=to_identifier(alias)) if alias else None
2440
2441        return Subquery(this=instance, alias=alias)
2442
2443    def limit(
2444        self, expression: ExpOrStr | int, dialect: DialectType = None, copy: bool = True, **opts
2445    ) -> Select:
2446        raise NotImplementedError
2447
2448    @property
2449    def ctes(self):
2450        with_ = self.args.get("with")
2451        if not with_:
2452            return []
2453        return with_.expressions
2454
2455    @property
2456    def selects(self) -> t.List[Expression]:
2457        raise NotImplementedError("Subqueryable objects must implement `selects`")
2458
2459    @property
2460    def named_selects(self) -> t.List[str]:
2461        raise NotImplementedError("Subqueryable objects must implement `named_selects`")
2462
2463    def select(
2464        self,
2465        *expressions: t.Optional[ExpOrStr],
2466        append: bool = True,
2467        dialect: DialectType = None,
2468        copy: bool = True,
2469        **opts,
2470    ) -> Subqueryable:
2471        raise NotImplementedError("Subqueryable objects must implement `select`")
2472
2473    def with_(
2474        self,
2475        alias: ExpOrStr,
2476        as_: ExpOrStr,
2477        recursive: t.Optional[bool] = None,
2478        append: bool = True,
2479        dialect: DialectType = None,
2480        copy: bool = True,
2481        **opts,
2482    ) -> Subqueryable:
2483        """
2484        Append to or set the common table expressions.
2485
2486        Example:
2487            >>> Select().with_("tbl2", as_="SELECT * FROM tbl").select("x").from_("tbl2").sql()
2488            'WITH tbl2 AS (SELECT * FROM tbl) SELECT x FROM tbl2'
2489
2490        Args:
2491            alias: the SQL code string to parse as the table name.
2492                If an `Expression` instance is passed, this is used as-is.
2493            as_: the SQL code string to parse as the table expression.
2494                If an `Expression` instance is passed, it will be used as-is.
2495            recursive: set the RECURSIVE part of the expression. Defaults to `False`.
2496            append: if `True`, add to any existing expressions.
2497                Otherwise, this resets the expressions.
2498            dialect: the dialect used to parse the input expression.
2499            copy: if `False`, modify this expression instance in-place.
2500            opts: other options to use to parse the input expressions.
2501
2502        Returns:
2503            The modified expression.
2504        """
2505        return _apply_cte_builder(
2506            self, alias, as_, recursive=recursive, append=append, dialect=dialect, copy=copy, **opts
2507        )
2508
2509
2510QUERY_MODIFIERS = {
2511    "match": False,
2512    "laterals": False,
2513    "joins": False,
2514    "connect": False,
2515    "pivots": False,
2516    "where": False,
2517    "group": False,
2518    "having": False,
2519    "qualify": False,
2520    "windows": False,
2521    "distribute": False,
2522    "sort": False,
2523    "cluster": False,
2524    "order": False,
2525    "limit": False,
2526    "offset": False,
2527    "locks": False,
2528    "sample": False,
2529    "settings": False,
2530    "format": False,
2531}
2532
2533
2534# https://learn.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-table?view=sql-server-ver16
2535class WithTableHint(Expression):
2536    arg_types = {"expressions": True}
2537
2538
2539# https://dev.mysql.com/doc/refman/8.0/en/index-hints.html
2540class IndexTableHint(Expression):
2541    arg_types = {"this": True, "expressions": False, "target": False}
2542
2543
2544# https://docs.snowflake.com/en/sql-reference/constructs/at-before
2545class HistoricalData(Expression):
2546    arg_types = {"this": True, "kind": True, "expression": True}
2547
2548
2549class Table(Expression):
2550    arg_types = {
2551        "this": True,
2552        "alias": False,
2553        "db": False,
2554        "catalog": False,
2555        "laterals": False,
2556        "joins": False,
2557        "pivots": False,
2558        "hints": False,
2559        "system_time": False,
2560        "version": False,
2561        "format": False,
2562        "pattern": False,
2563        "ordinality": False,
2564        "when": False,
2565    }
2566
2567    @property
2568    def name(self) -> str:
2569        if isinstance(self.this, Func):
2570            return ""
2571        return self.this.name
2572
2573    @property
2574    def db(self) -> str:
2575        return self.text("db")
2576
2577    @property
2578    def catalog(self) -> str:
2579        return self.text("catalog")
2580
2581    @property
2582    def selects(self) -> t.List[Expression]:
2583        return []
2584
2585    @property
2586    def named_selects(self) -> t.List[str]:
2587        return []
2588
2589    @property
2590    def parts(self) -> t.List[Expression]:
2591        """Return the parts of a table in order catalog, db, table."""
2592        parts: t.List[Expression] = []
2593
2594        for arg in ("catalog", "db", "this"):
2595            part = self.args.get(arg)
2596
2597            if isinstance(part, Dot):
2598                parts.extend(part.flatten())
2599            elif isinstance(part, Expression):
2600                parts.append(part)
2601
2602        return parts
2603
2604    def to_column(self, copy: bool = True) -> Alias | Column | Dot:
2605        parts = self.parts
2606        col = column(*reversed(parts[0:4]), fields=parts[4:], copy=copy)  # type: ignore
2607        alias = self.args.get("alias")
2608        if alias:
2609            col = alias_(col, alias.this, copy=copy)
2610        return col
2611
2612
2613class Union(Subqueryable):
2614    arg_types = {
2615        "with": False,
2616        "this": True,
2617        "expression": True,
2618        "distinct": False,
2619        "by_name": False,
2620        **QUERY_MODIFIERS,
2621    }
2622
2623    def limit(
2624        self, expression: ExpOrStr | int, dialect: DialectType = None, copy: bool = True, **opts
2625    ) -> Select:
2626        """
2627        Set the LIMIT expression.
2628
2629        Example:
2630            >>> select("1").union(select("1")).limit(1).sql()
2631            'SELECT * FROM (SELECT 1 UNION SELECT 1) AS _l_0 LIMIT 1'
2632
2633        Args:
2634            expression: the SQL code string to parse.
2635                This can also be an integer.
2636                If a `Limit` instance is passed, this is used as-is.
2637                If another `Expression` instance is passed, it will be wrapped in a `Limit`.
2638            dialect: the dialect used to parse the input expression.
2639            copy: if `False`, modify this expression instance in-place.
2640            opts: other options to use to parse the input expressions.
2641
2642        Returns:
2643            The limited subqueryable.
2644        """
2645        return (
2646            select("*")
2647            .from_(self.subquery(alias="_l_0", copy=copy))
2648            .limit(expression, dialect=dialect, copy=False, **opts)
2649        )
2650
2651    def select(
2652        self,
2653        *expressions: t.Optional[ExpOrStr],
2654        append: bool = True,
2655        dialect: DialectType = None,
2656        copy: bool = True,
2657        **opts,
2658    ) -> Union:
2659        """Append to or set the SELECT of the union recursively.
2660
2661        Example:
2662            >>> from sqlglot import parse_one
2663            >>> parse_one("select a from x union select a from y union select a from z").select("b").sql()
2664            'SELECT a, b FROM x UNION SELECT a, b FROM y UNION SELECT a, b FROM z'
2665
2666        Args:
2667            *expressions: the SQL code strings to parse.
2668                If an `Expression` instance is passed, it will be used as-is.
2669            append: if `True`, add to any existing expressions.
2670                Otherwise, this resets the expressions.
2671            dialect: the dialect used to parse the input expressions.
2672            copy: if `False`, modify this expression instance in-place.
2673            opts: other options to use to parse the input expressions.
2674
2675        Returns:
2676            Union: the modified expression.
2677        """
2678        this = self.copy() if copy else self
2679        this.this.unnest().select(*expressions, append=append, dialect=dialect, copy=False, **opts)
2680        this.expression.unnest().select(
2681            *expressions, append=append, dialect=dialect, copy=False, **opts
2682        )
2683        return this
2684
2685    @property
2686    def named_selects(self) -> t.List[str]:
2687        return self.this.unnest().named_selects
2688
2689    @property
2690    def is_star(self) -> bool:
2691        return self.this.is_star or self.expression.is_star
2692
2693    @property
2694    def selects(self) -> t.List[Expression]:
2695        return self.this.unnest().selects
2696
2697    @property
2698    def left(self) -> Expression:
2699        return self.this
2700
2701    @property
2702    def right(self) -> Expression:
2703        return self.expression
2704
2705
2706class Except(Union):
2707    pass
2708
2709
2710class Intersect(Union):
2711    pass
2712
2713
2714class Unnest(UDTF):
2715    arg_types = {
2716        "expressions": True,
2717        "alias": False,
2718        "offset": False,
2719    }
2720
2721    @property
2722    def selects(self) -> t.List[Expression]:
2723        columns = super().selects
2724        offset = self.args.get("offset")
2725        if offset:
2726            columns = columns + [to_identifier("offset") if offset is True else offset]
2727        return columns
2728
2729
2730class Update(Expression):
2731    arg_types = {
2732        "with": False,
2733        "this": False,
2734        "expressions": True,
2735        "from": False,
2736        "where": False,
2737        "returning": False,
2738        "order": False,
2739        "limit": False,
2740    }
2741
2742
2743class Values(UDTF):
2744    arg_types = {"expressions": True, "alias": False}
2745
2746
2747class Var(Expression):
2748    pass
2749
2750
2751class Version(Expression):
2752    """
2753    Time travel, iceberg, bigquery etc
2754    https://trino.io/docs/current/connector/iceberg.html?highlight=snapshot#using-snapshots
2755    https://www.databricks.com/blog/2019/02/04/introducing-delta-time-travel-for-large-scale-data-lakes.html
2756    https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#for_system_time_as_of
2757    https://learn.microsoft.com/en-us/sql/relational-databases/tables/querying-data-in-a-system-versioned-temporal-table?view=sql-server-ver16
2758    this is either TIMESTAMP or VERSION
2759    kind is ("AS OF", "BETWEEN")
2760    """
2761
2762    arg_types = {"this": True, "kind": True, "expression": False}
2763
2764
2765class Schema(Expression):
2766    arg_types = {"this": False, "expressions": False}
2767
2768
2769# https://dev.mysql.com/doc/refman/8.0/en/select.html
2770# https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/SELECT.html
2771class Lock(Expression):
2772    arg_types = {"update": True, "expressions": False, "wait": False}
2773
2774
2775class Select(Subqueryable):
2776    arg_types = {
2777        "with": False,
2778        "kind": False,
2779        "expressions": False,
2780        "hint": False,
2781        "distinct": False,
2782        "into": False,
2783        "from": False,
2784        **QUERY_MODIFIERS,
2785    }
2786
2787    def from_(
2788        self, expression: ExpOrStr, dialect: DialectType = None, copy: bool = True, **opts
2789    ) -> Select:
2790        """
2791        Set the FROM expression.
2792
2793        Example:
2794            >>> Select().from_("tbl").select("x").sql()
2795            'SELECT x FROM tbl'
2796
2797        Args:
2798            expression : the SQL code strings to parse.
2799                If a `From` instance is passed, this is used as-is.
2800                If another `Expression` instance is passed, it will be wrapped in a `From`.
2801            dialect: the dialect used to parse the input expression.
2802            copy: if `False`, modify this expression instance in-place.
2803            opts: other options to use to parse the input expressions.
2804
2805        Returns:
2806            The modified Select expression.
2807        """
2808        return _apply_builder(
2809            expression=expression,
2810            instance=self,
2811            arg="from",
2812            into=From,
2813            prefix="FROM",
2814            dialect=dialect,
2815            copy=copy,
2816            **opts,
2817        )
2818
2819    def group_by(
2820        self,
2821        *expressions: t.Optional[ExpOrStr],
2822        append: bool = True,
2823        dialect: DialectType = None,
2824        copy: bool = True,
2825        **opts,
2826    ) -> Select:
2827        """
2828        Set the GROUP BY expression.
2829
2830        Example:
2831            >>> Select().from_("tbl").select("x", "COUNT(1)").group_by("x").sql()
2832            'SELECT x, COUNT(1) FROM tbl GROUP BY x'
2833
2834        Args:
2835            *expressions: the SQL code strings to parse.
2836                If a `Group` instance is passed, this is used as-is.
2837                If another `Expression` instance is passed, it will be wrapped in a `Group`.
2838                If nothing is passed in then a group by is not applied to the expression
2839            append: if `True`, add to any existing expressions.
2840                Otherwise, this flattens all the `Group` expression into a single expression.
2841            dialect: the dialect used to parse the input expression.
2842            copy: if `False`, modify this expression instance in-place.
2843            opts: other options to use to parse the input expressions.
2844
2845        Returns:
2846            The modified Select expression.
2847        """
2848        if not expressions:
2849            return self if not copy else self.copy()
2850
2851        return _apply_child_list_builder(
2852            *expressions,
2853            instance=self,
2854            arg="group",
2855            append=append,
2856            copy=copy,
2857            prefix="GROUP BY",
2858            into=Group,
2859            dialect=dialect,
2860            **opts,
2861        )
2862
2863    def order_by(
2864        self,
2865        *expressions: t.Optional[ExpOrStr],
2866        append: bool = True,
2867        dialect: DialectType = None,
2868        copy: bool = True,
2869        **opts,
2870    ) -> Select:
2871        """
2872        Set the ORDER BY expression.
2873
2874        Example:
2875            >>> Select().from_("tbl").select("x").order_by("x DESC").sql()
2876            'SELECT x FROM tbl ORDER BY x DESC'
2877
2878        Args:
2879            *expressions: the SQL code strings to parse.
2880                If a `Group` instance is passed, this is used as-is.
2881                If another `Expression` instance is passed, it will be wrapped in a `Order`.
2882            append: if `True`, add to any existing expressions.
2883                Otherwise, this flattens all the `Order` expression into a single expression.
2884            dialect: the dialect used to parse the input expression.
2885            copy: if `False`, modify this expression instance in-place.
2886            opts: other options to use to parse the input expressions.
2887
2888        Returns:
2889            The modified Select expression.
2890        """
2891        return _apply_child_list_builder(
2892            *expressions,
2893            instance=self,
2894            arg="order",
2895            append=append,
2896            copy=copy,
2897            prefix="ORDER BY",
2898            into=Order,
2899            dialect=dialect,
2900            **opts,
2901        )
2902
2903    def sort_by(
2904        self,
2905        *expressions: t.Optional[ExpOrStr],
2906        append: bool = True,
2907        dialect: DialectType = None,
2908        copy: bool = True,
2909        **opts,
2910    ) -> Select:
2911        """
2912        Set the SORT BY expression.
2913
2914        Example:
2915            >>> Select().from_("tbl").select("x").sort_by("x DESC").sql(dialect="hive")
2916            'SELECT x FROM tbl SORT BY x DESC'
2917
2918        Args:
2919            *expressions: the SQL code strings to parse.
2920                If a `Group` instance is passed, this is used as-is.
2921                If another `Expression` instance is passed, it will be wrapped in a `SORT`.
2922            append: if `True`, add to any existing expressions.
2923                Otherwise, this flattens all the `Order` expression into a single expression.
2924            dialect: the dialect used to parse the input expression.
2925            copy: if `False`, modify this expression instance in-place.
2926            opts: other options to use to parse the input expressions.
2927
2928        Returns:
2929            The modified Select expression.
2930        """
2931        return _apply_child_list_builder(
2932            *expressions,
2933            instance=self,
2934            arg="sort",
2935            append=append,
2936            copy=copy,
2937            prefix="SORT BY",
2938            into=Sort,
2939            dialect=dialect,
2940            **opts,
2941        )
2942
2943    def cluster_by(
2944        self,
2945        *expressions: t.Optional[ExpOrStr],
2946        append: bool = True,
2947        dialect: DialectType = None,
2948        copy: bool = True,
2949        **opts,
2950    ) -> Select:
2951        """
2952        Set the CLUSTER BY expression.
2953
2954        Example:
2955            >>> Select().from_("tbl").select("x").cluster_by("x DESC").sql(dialect="hive")
2956            'SELECT x FROM tbl CLUSTER BY x DESC'
2957
2958        Args:
2959            *expressions: the SQL code strings to parse.
2960                If a `Group` instance is passed, this is used as-is.
2961                If another `Expression` instance is passed, it will be wrapped in a `Cluster`.
2962            append: if `True`, add to any existing expressions.
2963                Otherwise, this flattens all the `Order` expression into a single expression.
2964            dialect: the dialect used to parse the input expression.
2965            copy: if `False`, modify this expression instance in-place.
2966            opts: other options to use to parse the input expressions.
2967
2968        Returns:
2969            The modified Select expression.
2970        """
2971        return _apply_child_list_builder(
2972            *expressions,
2973            instance=self,
2974            arg="cluster",
2975            append=append,
2976            copy=copy,
2977            prefix="CLUSTER BY",
2978            into=Cluster,
2979            dialect=dialect,
2980            **opts,
2981        )
2982
2983    def limit(
2984        self, expression: ExpOrStr | int, dialect: DialectType = None, copy: bool = True, **opts
2985    ) -> Select:
2986        """
2987        Set the LIMIT expression.
2988
2989        Example:
2990            >>> Select().from_("tbl").select("x").limit(10).sql()
2991            'SELECT x FROM tbl LIMIT 10'
2992
2993        Args:
2994            expression: the SQL code string to parse.
2995                This can also be an integer.
2996                If a `Limit` instance is passed, this is used as-is.
2997                If another `Expression` instance is passed, it will be wrapped in a `Limit`.
2998            dialect: the dialect used to parse the input expression.
2999            copy: if `False`, modify this expression instance in-place.
3000            opts: other options to use to parse the input expressions.
3001
3002        Returns:
3003            Select: the modified expression.
3004        """
3005        return _apply_builder(
3006            expression=expression,
3007            instance=self,
3008            arg="limit",
3009            into=Limit,
3010            prefix="LIMIT",
3011            dialect=dialect,
3012            copy=copy,
3013            into_arg="expression",
3014            **opts,
3015        )
3016
3017    def offset(
3018        self, expression: ExpOrStr | int, dialect: DialectType = None, copy: bool = True, **opts
3019    ) -> Select:
3020        """
3021        Set the OFFSET expression.
3022
3023        Example:
3024            >>> Select().from_("tbl").select("x").offset(10).sql()
3025            'SELECT x FROM tbl OFFSET 10'
3026
3027        Args:
3028            expression: the SQL code string to parse.
3029                This can also be an integer.
3030                If a `Offset` instance is passed, this is used as-is.
3031                If another `Expression` instance is passed, it will be wrapped in a `Offset`.
3032            dialect: the dialect used to parse the input expression.
3033            copy: if `False`, modify this expression instance in-place.
3034            opts: other options to use to parse the input expressions.
3035
3036        Returns:
3037            The modified Select expression.
3038        """
3039        return _apply_builder(
3040            expression=expression,
3041            instance=self,
3042            arg="offset",
3043            into=Offset,
3044            prefix="OFFSET",
3045            dialect=dialect,
3046            copy=copy,
3047            into_arg="expression",
3048            **opts,
3049        )
3050
3051    def select(
3052        self,
3053        *expressions: t.Optional[ExpOrStr],
3054        append: bool = True,
3055        dialect: DialectType = None,
3056        copy: bool = True,
3057        **opts,
3058    ) -> Select:
3059        """
3060        Append to or set the SELECT expressions.
3061
3062        Example:
3063            >>> Select().select("x", "y").sql()
3064            'SELECT x, y'
3065
3066        Args:
3067            *expressions: the SQL code strings to parse.
3068                If an `Expression` instance is passed, it will be used as-is.
3069            append: if `True`, add to any existing expressions.
3070                Otherwise, this resets the expressions.
3071            dialect: the dialect used to parse the input expressions.
3072            copy: if `False`, modify this expression instance in-place.
3073            opts: other options to use to parse the input expressions.
3074
3075        Returns:
3076            The modified Select expression.
3077        """
3078        return _apply_list_builder(
3079            *expressions,
3080            instance=self,
3081            arg="expressions",
3082            append=append,
3083            dialect=dialect,
3084            copy=copy,
3085            **opts,
3086        )
3087
3088    def lateral(
3089        self,
3090        *expressions: t.Optional[ExpOrStr],
3091        append: bool = True,
3092        dialect: DialectType = None,
3093        copy: bool = True,
3094        **opts,
3095    ) -> Select:
3096        """
3097        Append to or set the LATERAL expressions.
3098
3099        Example:
3100            >>> Select().select("x").lateral("OUTER explode(y) tbl2 AS z").from_("tbl").sql()
3101            'SELECT x FROM tbl LATERAL VIEW OUTER EXPLODE(y) tbl2 AS z'
3102
3103        Args:
3104            *expressions: the SQL code strings to parse.
3105                If an `Expression` instance is passed, it will be used as-is.
3106            append: if `True`, add to any existing expressions.
3107                Otherwise, this resets the expressions.
3108            dialect: the dialect used to parse the input expressions.
3109            copy: if `False`, modify this expression instance in-place.
3110            opts: other options to use to parse the input expressions.
3111
3112        Returns:
3113            The modified Select expression.
3114        """
3115        return _apply_list_builder(
3116            *expressions,
3117            instance=self,
3118            arg="laterals",
3119            append=append,
3120            into=Lateral,
3121            prefix="LATERAL VIEW",
3122            dialect=dialect,
3123            copy=copy,
3124            **opts,
3125        )
3126
3127    def join(
3128        self,
3129        expression: ExpOrStr,
3130        on: t.Optional[ExpOrStr] = None,
3131        using: t.Optional[ExpOrStr | t.Collection[ExpOrStr]] = None,
3132        append: bool = True,
3133        join_type: t.Optional[str] = None,
3134        join_alias: t.Optional[Identifier | str] = None,
3135        dialect: DialectType = None,
3136        copy: bool = True,
3137        **opts,
3138    ) -> Select:
3139        """
3140        Append to or set the JOIN expressions.
3141
3142        Example:
3143            >>> Select().select("*").from_("tbl").join("tbl2", on="tbl1.y = tbl2.y").sql()
3144            'SELECT * FROM tbl JOIN tbl2 ON tbl1.y = tbl2.y'
3145
3146            >>> Select().select("1").from_("a").join("b", using=["x", "y", "z"]).sql()
3147            'SELECT 1 FROM a JOIN b USING (x, y, z)'
3148
3149            Use `join_type` to change the type of join:
3150
3151            >>> Select().select("*").from_("tbl").join("tbl2", on="tbl1.y = tbl2.y", join_type="left outer").sql()
3152            'SELECT * FROM tbl LEFT OUTER JOIN tbl2 ON tbl1.y = tbl2.y'
3153
3154        Args:
3155            expression: the SQL code string to parse.
3156                If an `Expression` instance is passed, it will be used as-is.
3157            on: optionally specify the join "on" criteria as a SQL string.
3158                If an `Expression` instance is passed, it will be used as-is.
3159            using: optionally specify the join "using" criteria as a SQL string.
3160                If an `Expression` instance is passed, it will be used as-is.
3161            append: if `True`, add to any existing expressions.
3162                Otherwise, this resets the expressions.
3163            join_type: if set, alter the parsed join type.
3164            join_alias: an optional alias for the joined source.
3165            dialect: the dialect used to parse the input expressions.
3166            copy: if `False`, modify this expression instance in-place.
3167            opts: other options to use to parse the input expressions.
3168
3169        Returns:
3170            Select: the modified expression.
3171        """
3172        parse_args: t.Dict[str, t.Any] = {"dialect": dialect, **opts}
3173
3174        try:
3175            expression = maybe_parse(expression, into=Join, prefix="JOIN", **parse_args)
3176        except ParseError:
3177            expression = maybe_parse(expression, into=(Join, Expression), **parse_args)
3178
3179        join = expression if isinstance(expression, Join) else Join(this=expression)
3180
3181        if isinstance(join.this, Select):
3182            join.this.replace(join.this.subquery())
3183
3184        if join_type:
3185            method: t.Optional[Token]
3186            side: t.Optional[Token]
3187            kind: t.Optional[Token]
3188
3189            method, side, kind = maybe_parse(join_type, into="JOIN_TYPE", **parse_args)  # type: ignore
3190
3191            if method:
3192                join.set("method", method.text)
3193            if side:
3194                join.set("side", side.text)
3195            if kind:
3196                join.set("kind", kind.text)
3197
3198        if on:
3199            on = and_(*ensure_list(on), dialect=dialect, copy=copy, **opts)
3200            join.set("on", on)
3201
3202        if using:
3203            join = _apply_list_builder(
3204                *ensure_list(using),
3205                instance=join,
3206                arg="using",
3207                append=append,
3208                copy=copy,
3209                into=Identifier,
3210                **opts,
3211            )
3212
3213        if join_alias:
3214            join.set("this", alias_(join.this, join_alias, table=True))
3215
3216        return _apply_list_builder(
3217            join,
3218            instance=self,
3219            arg="joins",
3220            append=append,
3221            copy=copy,
3222            **opts,
3223        )
3224
3225    def where(
3226        self,
3227        *expressions: t.Optional[ExpOrStr],
3228        append: bool = True,
3229        dialect: DialectType = None,
3230        copy: bool = True,
3231        **opts,
3232    ) -> Select:
3233        """
3234        Append to or set the WHERE expressions.
3235
3236        Example:
3237            >>> Select().select("x").from_("tbl").where("x = 'a' OR x < 'b'").sql()
3238            "SELECT x FROM tbl WHERE x = 'a' OR x < 'b'"
3239
3240        Args:
3241            *expressions: the SQL code strings to parse.
3242                If an `Expression` instance is passed, it will be used as-is.
3243                Multiple expressions are combined with an AND operator.
3244            append: if `True`, AND the new expressions to any existing expression.
3245                Otherwise, this resets the expression.
3246            dialect: the dialect used to parse the input expressions.
3247            copy: if `False`, modify this expression instance in-place.
3248            opts: other options to use to parse the input expressions.
3249
3250        Returns:
3251            Select: the modified expression.
3252        """
3253        return _apply_conjunction_builder(
3254            *expressions,
3255            instance=self,
3256            arg="where",
3257            append=append,
3258            into=Where,
3259            dialect=dialect,
3260            copy=copy,
3261            **opts,
3262        )
3263
3264    def having(
3265        self,
3266        *expressions: t.Optional[ExpOrStr],
3267        append: bool = True,
3268        dialect: DialectType = None,
3269        copy: bool = True,
3270        **opts,
3271    ) -> Select:
3272        """
3273        Append to or set the HAVING expressions.
3274
3275        Example:
3276            >>> Select().select("x", "COUNT(y)").from_("tbl").group_by("x").having("COUNT(y) > 3").sql()
3277            'SELECT x, COUNT(y) FROM tbl GROUP BY x HAVING COUNT(y) > 3'
3278
3279        Args:
3280            *expressions: the SQL code strings to parse.
3281                If an `Expression` instance is passed, it will be used as-is.
3282                Multiple expressions are combined with an AND operator.
3283            append: if `True`, AND the new expressions to any existing expression.
3284                Otherwise, this resets the expression.
3285            dialect: the dialect used to parse the input expressions.
3286            copy: if `False`, modify this expression instance in-place.
3287            opts: other options to use to parse the input expressions.
3288
3289        Returns:
3290            The modified Select expression.
3291        """
3292        return _apply_conjunction_builder(
3293            *expressions,
3294            instance=self,
3295            arg="having",
3296            append=append,
3297            into=Having,
3298            dialect=dialect,
3299            copy=copy,
3300            **opts,
3301        )
3302
3303    def window(
3304        self,
3305        *expressions: t.Optional[ExpOrStr],
3306        append: bool = True,
3307        dialect: DialectType = None,
3308        copy: bool = True,
3309        **opts,
3310    ) -> Select:
3311        return _apply_list_builder(
3312            *expressions,
3313            instance=self,
3314            arg="windows",
3315            append=append,
3316            into=Window,
3317            dialect=dialect,
3318            copy=copy,
3319            **opts,
3320        )
3321
3322    def qualify(
3323        self,
3324        *expressions: t.Optional[ExpOrStr],
3325        append: bool = True,
3326        dialect: DialectType = None,
3327        copy: bool = True,
3328        **opts,
3329    ) -> Select:
3330        return _apply_conjunction_builder(
3331            *expressions,
3332            instance=self,
3333            arg="qualify",
3334            append=append,
3335            into=Qualify,
3336            dialect=dialect,
3337            copy=copy,
3338            **opts,
3339        )
3340
3341    def distinct(
3342        self, *ons: t.Optional[ExpOrStr], distinct: bool = True, copy: bool = True
3343    ) -> Select:
3344        """
3345        Set the OFFSET expression.
3346
3347        Example:
3348            >>> Select().from_("tbl").select("x").distinct().sql()
3349            'SELECT DISTINCT x FROM tbl'
3350
3351        Args:
3352            ons: the expressions to distinct on
3353            distinct: whether the Select should be distinct
3354            copy: if `False`, modify this expression instance in-place.
3355
3356        Returns:
3357            Select: the modified expression.
3358        """
3359        instance = maybe_copy(self, copy)
3360        on = Tuple(expressions=[maybe_parse(on, copy=copy) for on in ons if on]) if ons else None
3361        instance.set("distinct", Distinct(on=on) if distinct else None)
3362        return instance
3363
3364    def ctas(
3365        self,
3366        table: ExpOrStr,
3367        properties: t.Optional[t.Dict] = None,
3368        dialect: DialectType = None,
3369        copy: bool = True,
3370        **opts,
3371    ) -> Create:
3372        """
3373        Convert this expression to a CREATE TABLE AS statement.
3374
3375        Example:
3376            >>> Select().select("*").from_("tbl").ctas("x").sql()
3377            'CREATE TABLE x AS SELECT * FROM tbl'
3378
3379        Args:
3380            table: the SQL code string to parse as the table name.
3381                If another `Expression` instance is passed, it will be used as-is.
3382            properties: an optional mapping of table properties
3383            dialect: the dialect used to parse the input table.
3384            copy: if `False`, modify this expression instance in-place.
3385            opts: other options to use to parse the input table.
3386
3387        Returns:
3388            The new Create expression.
3389        """
3390        instance = maybe_copy(self, copy)
3391        table_expression = maybe_parse(
3392            table,
3393            into=Table,
3394            dialect=dialect,
3395            **opts,
3396        )
3397        properties_expression = None
3398        if properties:
3399            properties_expression = Properties.from_dict(properties)
3400
3401        return Create(
3402            this=table_expression,
3403            kind="TABLE",
3404            expression=instance,
3405            properties=properties_expression,
3406        )
3407
3408    def lock(self, update: bool = True, copy: bool = True) -> Select:
3409        """
3410        Set the locking read mode for this expression.
3411
3412        Examples:
3413            >>> Select().select("x").from_("tbl").where("x = 'a'").lock().sql("mysql")
3414            "SELECT x FROM tbl WHERE x = 'a' FOR UPDATE"
3415
3416            >>> Select().select("x").from_("tbl").where("x = 'a'").lock(update=False).sql("mysql")
3417            "SELECT x FROM tbl WHERE x = 'a' FOR SHARE"
3418
3419        Args:
3420            update: if `True`, the locking type will be `FOR UPDATE`, else it will be `FOR SHARE`.
3421            copy: if `False`, modify this expression instance in-place.
3422
3423        Returns:
3424            The modified expression.
3425        """
3426        inst = maybe_copy(self, copy)
3427        inst.set("locks", [Lock(update=update)])
3428
3429        return inst
3430
3431    def hint(self, *hints: ExpOrStr, dialect: DialectType = None, copy: bool = True) -> Select:
3432        """
3433        Set hints for this expression.
3434
3435        Examples:
3436            >>> Select().select("x").from_("tbl").hint("BROADCAST(y)").sql(dialect="spark")
3437            'SELECT /*+ BROADCAST(y) */ x FROM tbl'
3438
3439        Args:
3440            hints: The SQL code strings to parse as the hints.
3441                If an `Expression` instance is passed, it will be used as-is.
3442            dialect: The dialect used to parse the hints.
3443            copy: If `False`, modify this expression instance in-place.
3444
3445        Returns:
3446            The modified expression.
3447        """
3448        inst = maybe_copy(self, copy)
3449        inst.set(
3450            "hint", Hint(expressions=[maybe_parse(h, copy=copy, dialect=dialect) for h in hints])
3451        )
3452
3453        return inst
3454
3455    @property
3456    def named_selects(self) -> t.List[str]:
3457        return [e.output_name for e in self.expressions if e.alias_or_name]
3458
3459    @property
3460    def is_star(self) -> bool:
3461        return any(expression.is_star for expression in self.expressions)
3462
3463    @property
3464    def selects(self) -> t.List[Expression]:
3465        return self.expressions
3466
3467
3468class Subquery(DerivedTable, Unionable):
3469    arg_types = {
3470        "this": True,
3471        "alias": False,
3472        "with": False,
3473        **QUERY_MODIFIERS,
3474    }
3475
3476    def unnest(self):
3477        """
3478        Returns the first non subquery.
3479        """
3480        expression = self
3481        while isinstance(expression, Subquery):
3482            expression = expression.this
3483        return expression
3484
3485    def unwrap(self) -> Subquery:
3486        expression = self
3487        while expression.same_parent and expression.is_wrapper:
3488            expression = t.cast(Subquery, expression.parent)
3489        return expression
3490
3491    @property
3492    def is_wrapper(self) -> bool:
3493        """
3494        Whether this Subquery acts as a simple wrapper around another expression.
3495
3496        SELECT * FROM (((SELECT * FROM t)))
3497                      ^
3498                      This corresponds to a "wrapper" Subquery node
3499        """
3500        return all(v is None for k, v in self.args.items() if k != "this")
3501
3502    @property
3503    def is_star(self) -> bool:
3504        return self.this.is_star
3505
3506    @property
3507    def output_name(self) -> str:
3508        return self.alias
3509
3510
3511class TableSample(Expression):
3512    arg_types = {
3513        "this": False,
3514        "expressions": False,
3515        "method": False,
3516        "bucket_numerator": False,
3517        "bucket_denominator": False,
3518        "bucket_field": False,
3519        "percent": False,
3520        "rows": False,
3521        "size": False,
3522        "seed": False,
3523    }
3524
3525
3526class Tag(Expression):
3527    """Tags are used for generating arbitrary sql like SELECT <span>x</span>."""
3528
3529    arg_types = {
3530        "this": False,
3531        "prefix": False,
3532        "postfix": False,
3533    }
3534
3535
3536# Represents both the standard SQL PIVOT operator and DuckDB's "simplified" PIVOT syntax
3537# https://duckdb.org/docs/sql/statements/pivot
3538class Pivot(Expression):
3539    arg_types = {
3540        "this": False,
3541        "alias": False,
3542        "expressions": False,
3543        "field": False,
3544        "unpivot": False,
3545        "using": False,
3546        "group": False,
3547        "columns": False,
3548        "include_nulls": False,
3549    }
3550
3551    @property
3552    def unpivot(self) -> bool:
3553        return bool(self.args.get("unpivot"))
3554
3555
3556class Window(Condition):
3557    arg_types = {
3558        "this": True,
3559        "partition_by": False,
3560        "order": False,
3561        "spec": False,
3562        "alias": False,
3563        "over": False,
3564        "first": False,
3565    }
3566
3567
3568class WindowSpec(Expression):
3569    arg_types = {
3570        "kind": False,
3571        "start": False,
3572        "start_side": False,
3573        "end": False,
3574        "end_side": False,
3575    }
3576
3577
3578class Where(Expression):
3579    pass
3580
3581
3582class Star(Expression):
3583    arg_types = {"except": False, "replace": False}
3584
3585    @property
3586    def name(self) -> str:
3587        return "*"
3588
3589    @property
3590    def output_name(self) -> str:
3591        return self.name
3592
3593
3594class Parameter(Condition):
3595    arg_types = {"this": True, "expression": False}
3596
3597
3598class SessionParameter(Condition):
3599    arg_types = {"this": True, "kind": False}
3600
3601
3602class Placeholder(Condition):
3603    arg_types = {"this": False, "kind": False}
3604
3605
3606class Null(Condition):
3607    arg_types: t.Dict[str, t.Any] = {}
3608
3609    @property
3610    def name(self) -> str:
3611        return "NULL"
3612
3613
3614class Boolean(Condition):
3615    pass
3616
3617
3618class DataTypeParam(Expression):
3619    arg_types = {"this": True, "expression": False}
3620
3621
3622class DataType(Expression):
3623    arg_types = {
3624        "this": True,
3625        "expressions": False,
3626        "nested": False,
3627        "values": False,
3628        "prefix": False,
3629        "kind": False,
3630    }
3631
3632    class Type(AutoName):
3633        ARRAY = auto()
3634        BIGDECIMAL = auto()
3635        BIGINT = auto()
3636        BIGSERIAL = auto()
3637        BINARY = auto()
3638        BIT = auto()
3639        BOOLEAN = auto()
3640        CHAR = auto()
3641        DATE = auto()
3642        DATE32 = auto()
3643        DATEMULTIRANGE = auto()
3644        DATERANGE = auto()
3645        DATETIME = auto()
3646        DATETIME64 = auto()
3647        DECIMAL = auto()
3648        DOUBLE = auto()
3649        ENUM = auto()
3650        ENUM8 = auto()
3651        ENUM16 = auto()
3652        FIXEDSTRING = auto()
3653        FLOAT = auto()
3654        GEOGRAPHY = auto()
3655        GEOMETRY = auto()
3656        HLLSKETCH = auto()
3657        HSTORE = auto()
3658        IMAGE = auto()
3659        INET = auto()
3660        INT = auto()
3661        INT128 = auto()
3662        INT256 = auto()
3663        INT4MULTIRANGE = auto()
3664        INT4RANGE = auto()
3665        INT8MULTIRANGE = auto()
3666        INT8RANGE = auto()
3667        INTERVAL = auto()
3668        IPADDRESS = auto()
3669        IPPREFIX = auto()
3670        IPV4 = auto()
3671        IPV6 = auto()
3672        JSON = auto()
3673        JSONB = auto()
3674        LONGBLOB = auto()
3675        LONGTEXT = auto()
3676        LOWCARDINALITY = auto()
3677        MAP = auto()
3678        MEDIUMBLOB = auto()
3679        MEDIUMINT = auto()
3680        MEDIUMTEXT = auto()
3681        MONEY = auto()
3682        NCHAR = auto()
3683        NESTED = auto()
3684        NULL = auto()
3685        NULLABLE = auto()
3686        NUMMULTIRANGE = auto()
3687        NUMRANGE = auto()
3688        NVARCHAR = auto()
3689        OBJECT = auto()
3690        ROWVERSION = auto()
3691        SERIAL = auto()
3692        SET = auto()
3693        SMALLINT = auto()
3694        SMALLMONEY = auto()
3695        SMALLSERIAL = auto()
3696        STRUCT = auto()
3697        SUPER = auto()
3698        TEXT = auto()
3699        TINYBLOB = auto()
3700        TINYTEXT = auto()
3701        TIME = auto()
3702        TIMETZ = auto()
3703        TIMESTAMP = auto()
3704        TIMESTAMPLTZ = auto()
3705        TIMESTAMPTZ = auto()
3706        TIMESTAMP_S = auto()
3707        TIMESTAMP_MS = auto()
3708        TIMESTAMP_NS = auto()
3709        TINYINT = auto()
3710        TSMULTIRANGE = auto()
3711        TSRANGE = auto()
3712        TSTZMULTIRANGE = auto()
3713        TSTZRANGE = auto()
3714        UBIGINT = auto()
3715        UINT = auto()
3716        UINT128 = auto()
3717        UINT256 = auto()
3718        UMEDIUMINT = auto()
3719        UDECIMAL = auto()
3720        UNIQUEIDENTIFIER = auto()
3721        UNKNOWN = auto()  # Sentinel value, useful for type annotation
3722        USERDEFINED = "USER-DEFINED"
3723        USMALLINT = auto()
3724        UTINYINT = auto()
3725        UUID = auto()
3726        VARBINARY = auto()
3727        VARCHAR = auto()
3728        VARIANT = auto()
3729        XML = auto()
3730        YEAR = auto()
3731
3732    TEXT_TYPES = {
3733        Type.CHAR,
3734        Type.NCHAR,
3735        Type.VARCHAR,
3736        Type.NVARCHAR,
3737        Type.TEXT,
3738    }
3739
3740    INTEGER_TYPES = {
3741        Type.INT,
3742        Type.TINYINT,
3743        Type.SMALLINT,
3744        Type.BIGINT,
3745        Type.INT128,
3746        Type.INT256,
3747        Type.BIT,
3748    }
3749
3750    FLOAT_TYPES = {
3751        Type.FLOAT,
3752        Type.DOUBLE,
3753    }
3754
3755    NUMERIC_TYPES = {
3756        *INTEGER_TYPES,
3757        *FLOAT_TYPES,
3758    }
3759
3760    TEMPORAL_TYPES = {
3761        Type.TIME,
3762        Type.TIMETZ,
3763        Type.TIMESTAMP,
3764        Type.TIMESTAMPTZ,
3765        Type.TIMESTAMPLTZ,
3766        Type.TIMESTAMP_S,
3767        Type.TIMESTAMP_MS,
3768        Type.TIMESTAMP_NS,
3769        Type.DATE,
3770        Type.DATE32,
3771        Type.DATETIME,
3772        Type.DATETIME64,
3773    }
3774
3775    @classmethod
3776    def build(
3777        cls,
3778        dtype: DATA_TYPE,
3779        dialect: DialectType = None,
3780        udt: bool = False,
3781        **kwargs,
3782    ) -> DataType:
3783        """
3784        Constructs a DataType object.
3785
3786        Args:
3787            dtype: the data type of interest.
3788            dialect: the dialect to use for parsing `dtype`, in case it's a string.
3789            udt: when set to True, `dtype` will be used as-is if it can't be parsed into a
3790                DataType, thus creating a user-defined type.
3791            kawrgs: additional arguments to pass in the constructor of DataType.
3792
3793        Returns:
3794            The constructed DataType object.
3795        """
3796        from sqlglot import parse_one
3797
3798        if isinstance(dtype, str):
3799            if dtype.upper() == "UNKNOWN":
3800                return DataType(this=DataType.Type.UNKNOWN, **kwargs)
3801
3802            try:
3803                data_type_exp = parse_one(
3804                    dtype, read=dialect, into=DataType, error_level=ErrorLevel.IGNORE
3805                )
3806            except ParseError:
3807                if udt:
3808                    return DataType(this=DataType.Type.USERDEFINED, kind=dtype, **kwargs)
3809                raise
3810        elif isinstance(dtype, DataType.Type):
3811            data_type_exp = DataType(this=dtype)
3812        elif isinstance(dtype, DataType):
3813            return dtype
3814        else:
3815            raise ValueError(f"Invalid data type: {type(dtype)}. Expected str or DataType.Type")
3816
3817        return DataType(**{**data_type_exp.args, **kwargs})
3818
3819    def is_type(self, *dtypes: DATA_TYPE) -> bool:
3820        """
3821        Checks whether this DataType matches one of the provided data types. Nested types or precision
3822        will be compared using "structural equivalence" semantics, so e.g. array<int> != array<float>.
3823
3824        Args:
3825            dtypes: the data types to compare this DataType to.
3826
3827        Returns:
3828            True, if and only if there is a type in `dtypes` which is equal to this DataType.
3829        """
3830        for dtype in dtypes:
3831            other = DataType.build(dtype, udt=True)
3832
3833            if (
3834                other.expressions
3835                or self.this == DataType.Type.USERDEFINED
3836                or other.this == DataType.Type.USERDEFINED
3837            ):
3838                matches = self == other
3839            else:
3840                matches = self.this == other.this
3841
3842            if matches:
3843                return True
3844        return False
3845
3846
3847DATA_TYPE = t.Union[str, DataType, DataType.Type]
3848
3849
3850# https://www.postgresql.org/docs/15/datatype-pseudo.html
3851class PseudoType(DataType):
3852    arg_types = {"this": True}
3853
3854
3855# https://www.postgresql.org/docs/15/datatype-oid.html
3856class ObjectIdentifier(DataType):
3857    arg_types = {"this": True}
3858
3859
3860# WHERE x <OP> EXISTS|ALL|ANY|SOME(SELECT ...)
3861class SubqueryPredicate(Predicate):
3862    pass
3863
3864
3865class All(SubqueryPredicate):
3866    pass
3867
3868
3869class Any(SubqueryPredicate):
3870    pass
3871
3872
3873class Exists(SubqueryPredicate):
3874    pass
3875
3876
3877# Commands to interact with the databases or engines. For most of the command
3878# expressions we parse whatever comes after the command's name as a string.
3879class Command(Expression):
3880    arg_types = {"this": True, "expression": False}
3881
3882
3883class Transaction(Expression):
3884    arg_types = {"this": False, "modes": False, "mark": False}
3885
3886
3887class Commit(Expression):
3888    arg_types = {"chain": False, "this": False, "durability": False}
3889
3890
3891class Rollback(Expression):
3892    arg_types = {"savepoint": False, "this": False}
3893
3894
3895class AlterTable(Expression):
3896    arg_types = {"this": True, "actions": True, "exists": False, "only": False}
3897
3898
3899class AddConstraint(Expression):
3900    arg_types = {"this": False, "expression": False, "enforced": False}
3901
3902
3903class DropPartition(Expression):
3904    arg_types = {"expressions": True, "exists": False}
3905
3906
3907# Binary expressions like (ADD a b)
3908class Binary(Condition):
3909    arg_types = {"this": True, "expression": True}
3910
3911    @property
3912    def left(self) -> Expression:
3913        return self.this
3914
3915    @property
3916    def right(self) -> Expression:
3917        return self.expression
3918
3919
3920class Add(Binary):
3921    pass
3922
3923
3924class Connector(Binary):
3925    pass
3926
3927
3928class And(Connector):
3929    pass
3930
3931
3932class Or(Connector):
3933    pass
3934
3935
3936class BitwiseAnd(Binary):
3937    pass
3938
3939
3940class BitwiseLeftShift(Binary):
3941    pass
3942
3943
3944class BitwiseOr(Binary):
3945    pass
3946
3947
3948class BitwiseRightShift(Binary):
3949    pass
3950
3951
3952class BitwiseXor(Binary):
3953    pass
3954
3955
3956class Div(Binary):
3957    arg_types = {"this": True, "expression": True, "typed": False, "safe": False}
3958
3959
3960class Overlaps(Binary):
3961    pass
3962
3963
3964class Dot(Binary):
3965    @property
3966    def name(self) -> str:
3967        return self.expression.name
3968
3969    @property
3970    def output_name(self) -> str:
3971        return self.name
3972
3973    @classmethod
3974    def build(self, expressions: t.Sequence[Expression]) -> Dot:
3975        """Build a Dot object with a sequence of expressions."""
3976        if len(expressions) < 2:
3977            raise ValueError(f"Dot requires >= 2 expressions.")
3978
3979        return t.cast(Dot, reduce(lambda x, y: Dot(this=x, expression=y), expressions))
3980
3981    @property
3982    def parts(self) -> t.List[Expression]:
3983        """Return the parts of a table / column in order catalog, db, table."""
3984        this, *parts = self.flatten()
3985
3986        parts.reverse()
3987
3988        for arg in ("this", "table", "db", "catalog"):
3989            part = this.args.get(arg)
3990
3991            if isinstance(part, Expression):
3992                parts.append(part)
3993
3994        parts.reverse()
3995        return parts
3996
3997
3998class DPipe(Binary):
3999    arg_types = {"this": True, "expression": True, "safe": False}
4000
4001
4002class EQ(Binary, Predicate):
4003    pass
4004
4005
4006class NullSafeEQ(Binary, Predicate):
4007    pass
4008
4009
4010class NullSafeNEQ(Binary, Predicate):
4011    pass
4012
4013
4014# Represents e.g. := in DuckDB which is mostly used for setting parameters
4015class PropertyEQ(Binary):
4016    pass
4017
4018
4019class Distance(Binary):
4020    pass
4021
4022
4023class Escape(Binary):
4024    pass
4025
4026
4027class Glob(Binary, Predicate):
4028    pass
4029
4030
4031class GT(Binary, Predicate):
4032    pass
4033
4034
4035class GTE(Binary, Predicate):
4036    pass
4037
4038
4039class ILike(Binary, Predicate):
4040    pass
4041
4042
4043class ILikeAny(Binary, Predicate):
4044    pass
4045
4046
4047class IntDiv(Binary):
4048    pass
4049
4050
4051class Is(Binary, Predicate):
4052    pass
4053
4054
4055class Kwarg(Binary):
4056    """Kwarg in special functions like func(kwarg => y)."""
4057
4058
4059class Like(Binary, Predicate):
4060    pass
4061
4062
4063class LikeAny(Binary, Predicate):
4064    pass
4065
4066
4067class LT(Binary, Predicate):
4068    pass
4069
4070
4071class LTE(Binary, Predicate):
4072    pass
4073
4074
4075class Mod(Binary):
4076    pass
4077
4078
4079class Mul(Binary):
4080    pass
4081
4082
4083class NEQ(Binary, Predicate):
4084    pass
4085
4086
4087# https://www.postgresql.org/docs/current/ddl-schemas.html#DDL-SCHEMAS-PATH
4088class Operator(Binary):
4089    arg_types = {"this": True, "operator": True, "expression": True}
4090
4091
4092class SimilarTo(Binary, Predicate):
4093    pass
4094
4095
4096class Slice(Binary):
4097    arg_types = {"this": False, "expression": False}
4098
4099
4100class Sub(Binary):
4101    pass
4102
4103
4104class ArrayOverlaps(Binary):
4105    pass
4106
4107
4108# Unary Expressions
4109# (NOT a)
4110class Unary(Condition):
4111    pass
4112
4113
4114class BitwiseNot(Unary):
4115    pass
4116
4117
4118class Not(Unary):
4119    pass
4120
4121
4122class Paren(Unary):
4123    arg_types = {"this": True, "with": False}
4124
4125    @property
4126    def output_name(self) -> str:
4127        return self.this.name
4128
4129
4130class Neg(Unary):
4131    pass
4132
4133
4134class Alias(Expression):
4135    arg_types = {"this": True, "alias": False}
4136
4137    @property
4138    def output_name(self) -> str:
4139        return self.alias
4140
4141
4142# BigQuery requires the UNPIVOT column list aliases to be either strings or ints, but
4143# other dialects require identifiers. This enables us to transpile between them easily.
4144class PivotAlias(Alias):
4145    pass
4146
4147
4148class Aliases(Expression):
4149    arg_types = {"this": True, "expressions": True}
4150
4151    @property
4152    def aliases(self):
4153        return self.expressions
4154
4155
4156# https://docs.aws.amazon.com/redshift/latest/dg/query-super.html
4157class AtIndex(Expression):
4158    arg_types = {"this": True, "expression": True}
4159
4160
4161class AtTimeZone(Expression):
4162    arg_types = {"this": True, "zone": True}
4163
4164
4165class Between(Predicate):
4166    arg_types = {"this": True, "low": True, "high": True}
4167
4168
4169class Bracket(Condition):
4170    # https://cloud.google.com/bigquery/docs/reference/standard-sql/operators#array_subscript_operator
4171    arg_types = {"this": True, "expressions": True, "offset": False, "safe": False}
4172
4173    @property
4174    def output_name(self) -> str:
4175        if len(self.expressions) == 1:
4176            return self.expressions[0].output_name
4177
4178        return super().output_name
4179
4180
4181class Distinct(Expression):
4182    arg_types = {"expressions": False, "on": False}
4183
4184
4185class In(Predicate):
4186    arg_types = {
4187        "this": True,
4188        "expressions": False,
4189        "query": False,
4190        "unnest": False,
4191        "field": False,
4192        "is_global": False,
4193    }
4194
4195
4196# https://cloud.google.com/bigquery/docs/reference/standard-sql/procedural-language#for-in
4197class ForIn(Expression):
4198    arg_types = {"this": True, "expression": True}
4199
4200
4201class TimeUnit(Expression):
4202    """Automatically converts unit arg into a var."""
4203
4204    arg_types = {"unit": False}
4205
4206    UNABBREVIATED_UNIT_NAME = {
4207        "D": "DAY",
4208        "H": "HOUR",
4209        "M": "MINUTE",
4210        "MS": "MILLISECOND",
4211        "NS": "NANOSECOND",
4212        "Q": "QUARTER",
4213        "S": "SECOND",
4214        "US": "MICROSECOND",
4215        "W": "WEEK",
4216        "Y": "YEAR",
4217    }
4218
4219    VAR_LIKE = (Column, Literal, Var)
4220
4221    def __init__(self, **args):
4222        unit = args.get("unit")
4223        if isinstance(unit, self.VAR_LIKE):
4224            args["unit"] = Var(
4225                this=(self.UNABBREVIATED_UNIT_NAME.get(unit.name) or unit.name).upper()
4226            )
4227        elif isinstance(unit, Week):
4228            unit.set("this", Var(this=unit.this.name.upper()))
4229
4230        super().__init__(**args)
4231
4232    @property
4233    def unit(self) -> t.Optional[Var]:
4234        return self.args.get("unit")
4235
4236
4237class IntervalOp(TimeUnit):
4238    arg_types = {"unit": True, "expression": True}
4239
4240    def interval(self):
4241        return Interval(
4242            this=self.expression.copy(),
4243            unit=self.unit.copy(),
4244        )
4245
4246
4247# https://www.oracletutorial.com/oracle-basics/oracle-interval/
4248# https://trino.io/docs/current/language/types.html#interval-day-to-second
4249# https://docs.databricks.com/en/sql/language-manual/data-types/interval-type.html
4250class IntervalSpan(DataType):
4251    arg_types = {"this": True, "expression": True}
4252
4253
4254class Interval(TimeUnit):
4255    arg_types = {"this": False, "unit": False}
4256
4257
4258class IgnoreNulls(Expression):
4259    pass
4260
4261
4262class RespectNulls(Expression):
4263    pass
4264
4265
4266# Functions
4267class Func(Condition):
4268    """
4269    The base class for all function expressions.
4270
4271    Attributes:
4272        is_var_len_args (bool): if set to True the last argument defined in arg_types will be
4273            treated as a variable length argument and the argument's value will be stored as a list.
4274        _sql_names (list): determines the SQL name (1st item in the list) and aliases (subsequent items)
4275            for this function expression. These values are used to map this node to a name during parsing
4276            as well as to provide the function's name during SQL string generation. By default the SQL
4277            name is set to the expression's class name transformed to snake case.
4278    """
4279
4280    is_var_len_args = False
4281
4282    @classmethod
4283    def from_arg_list(cls, args):
4284        if cls.is_var_len_args:
4285            all_arg_keys = list(cls.arg_types)
4286            # If this function supports variable length argument treat the last argument as such.
4287            non_var_len_arg_keys = all_arg_keys[:-1] if cls.is_var_len_args else all_arg_keys
4288            num_non_var = len(non_var_len_arg_keys)
4289
4290            args_dict = {arg_key: arg for arg, arg_key in zip(args, non_var_len_arg_keys)}
4291            args_dict[all_arg_keys[-1]] = args[num_non_var:]
4292        else:
4293            args_dict = {arg_key: arg for arg, arg_key in zip(args, cls.arg_types)}
4294
4295        return cls(**args_dict)
4296
4297    @classmethod
4298    def sql_names(cls):
4299        if cls is Func:
4300            raise NotImplementedError(
4301                "SQL name is only supported by concrete function implementations"
4302            )
4303        if "_sql_names" not in cls.__dict__:
4304            cls._sql_names = [camel_to_snake_case(cls.__name__)]
4305        return cls._sql_names
4306
4307    @classmethod
4308    def sql_name(cls):
4309        return cls.sql_names()[0]
4310
4311    @classmethod
4312    def default_parser_mappings(cls):
4313        return {name: cls.from_arg_list for name in cls.sql_names()}
4314
4315
4316class AggFunc(Func):
4317    pass
4318
4319
4320class ParameterizedAgg(AggFunc):
4321    arg_types = {"this": True, "expressions": True, "params": True}
4322
4323
4324class Abs(Func):
4325    pass
4326
4327
4328class ArgMax(AggFunc):
4329    arg_types = {"this": True, "expression": True, "count": False}
4330    _sql_names = ["ARG_MAX", "ARGMAX", "MAX_BY"]
4331
4332
4333class ArgMin(AggFunc):
4334    arg_types = {"this": True, "expression": True, "count": False}
4335    _sql_names = ["ARG_MIN", "ARGMIN", "MIN_BY"]
4336
4337
4338class ApproxTopK(AggFunc):
4339    arg_types = {"this": True, "expression": False, "counters": False}
4340
4341
4342class Flatten(Func):
4343    pass
4344
4345
4346# https://spark.apache.org/docs/latest/api/sql/index.html#transform
4347class Transform(Func):
4348    arg_types = {"this": True, "expression": True}
4349
4350
4351class Anonymous(Func):
4352    arg_types = {"this": True, "expressions": False}
4353    is_var_len_args = True
4354
4355
4356class AnonymousAggFunc(AggFunc):
4357    arg_types = {"this": True, "expressions": False}
4358    is_var_len_args = True
4359
4360
4361# https://clickhouse.com/docs/en/sql-reference/aggregate-functions/combinators
4362class CombinedAggFunc(AnonymousAggFunc):
4363    arg_types = {"this": True, "expressions": False, "parts": True}
4364
4365
4366class CombinedParameterizedAgg(ParameterizedAgg):
4367    arg_types = {"this": True, "expressions": True, "params": True, "parts": True}
4368
4369
4370# https://docs.snowflake.com/en/sql-reference/functions/hll
4371# https://docs.aws.amazon.com/redshift/latest/dg/r_HLL_function.html
4372class Hll(AggFunc):
4373    arg_types = {"this": True, "expressions": False}
4374    is_var_len_args = True
4375
4376
4377class ApproxDistinct(AggFunc):
4378    arg_types = {"this": True, "accuracy": False}
4379    _sql_names = ["APPROX_DISTINCT", "APPROX_COUNT_DISTINCT"]
4380
4381
4382class Array(Func):
4383    arg_types = {"expressions": False}
4384    is_var_len_args = True
4385
4386
4387# https://docs.snowflake.com/en/sql-reference/functions/to_array
4388class ToArray(Func):
4389    pass
4390
4391
4392# https://docs.snowflake.com/en/sql-reference/functions/to_char
4393# https://docs.oracle.com/en/database/oracle/oracle-database/23/sqlrf/TO_CHAR-number.html
4394class ToChar(Func):
4395    arg_types = {"this": True, "format": False, "nlsparam": False}
4396
4397
4398class GenerateSeries(Func):
4399    arg_types = {"start": True, "end": True, "step": False}
4400
4401
4402class ArrayAgg(AggFunc):
4403    pass
4404
4405
4406class ArrayUniqueAgg(AggFunc):
4407    pass
4408
4409
4410class ArrayAll(Func):
4411    arg_types = {"this": True, "expression": True}
4412
4413
4414class ArrayAny(Func):
4415    arg_types = {"this": True, "expression": True}
4416
4417
4418class ArrayConcat(Func):
4419    _sql_names = ["ARRAY_CONCAT", "ARRAY_CAT"]
4420    arg_types = {"this": True, "expressions": False}
4421    is_var_len_args = True
4422
4423
4424class ArrayContains(Binary, Func):
4425    pass
4426
4427
4428class ArrayContained(Binary):
4429    pass
4430
4431
4432class ArrayFilter(Func):
4433    arg_types = {"this": True, "expression": True}
4434    _sql_names = ["FILTER", "ARRAY_FILTER"]
4435
4436
4437class ArrayJoin(Func):
4438    arg_types = {"this": True, "expression": True, "null": False}
4439
4440
4441class ArraySize(Func):
4442    arg_types = {"this": True, "expression": False}
4443
4444
4445class ArraySort(Func):
4446    arg_types = {"this": True, "expression": False}
4447
4448
4449class ArraySum(Func):
4450    arg_types = {"this": True, "expression": False}
4451
4452
4453class ArrayUnionAgg(AggFunc):
4454    pass
4455
4456
4457class Avg(AggFunc):
4458    pass
4459
4460
4461class AnyValue(AggFunc):
4462    arg_types = {"this": True, "having": False, "max": False, "ignore_nulls": False}
4463
4464
4465class First(Func):
4466    arg_types = {"this": True, "ignore_nulls": False}
4467
4468
4469class Last(Func):
4470    arg_types = {"this": True, "ignore_nulls": False}
4471
4472
4473class Case(Func):
4474    arg_types = {"this": False, "ifs": True, "default": False}
4475
4476    def when(self, condition: ExpOrStr, then: ExpOrStr, copy: bool = True, **opts) -> Case:
4477        instance = maybe_copy(self, copy)
4478        instance.append(
4479            "ifs",
4480            If(
4481                this=maybe_parse(condition, copy=copy, **opts),
4482                true=maybe_parse(then, copy=copy, **opts),
4483            ),
4484        )
4485        return instance
4486
4487    def else_(self, condition: ExpOrStr, copy: bool = True, **opts) -> Case:
4488        instance = maybe_copy(self, copy)
4489        instance.set("default", maybe_parse(condition, copy=copy, **opts))
4490        return instance
4491
4492
4493class Cast(Func):
4494    arg_types = {"this": True, "to": True, "format": False, "safe": False}
4495
4496    @property
4497    def name(self) -> str:
4498        return self.this.name
4499
4500    @property
4501    def to(self) -> DataType:
4502        return self.args["to"]
4503
4504    @property
4505    def output_name(self) -> str:
4506        return self.name
4507
4508    def is_type(self, *dtypes: DATA_TYPE) -> bool:
4509        """
4510        Checks whether this Cast's DataType matches one of the provided data types. Nested types
4511        like arrays or structs will be compared using "structural equivalence" semantics, so e.g.
4512        array<int> != array<float>.
4513
4514        Args:
4515            dtypes: the data types to compare this Cast's DataType to.
4516
4517        Returns:
4518            True, if and only if there is a type in `dtypes` which is equal to this Cast's DataType.
4519        """
4520        return self.to.is_type(*dtypes)
4521
4522
4523class TryCast(Cast):
4524    pass
4525
4526
4527class CastToStrType(Func):
4528    arg_types = {"this": True, "to": True}
4529
4530
4531class Collate(Binary, Func):
4532    pass
4533
4534
4535class Ceil(Func):
4536    arg_types = {"this": True, "decimals": False}
4537    _sql_names = ["CEIL", "CEILING"]
4538
4539
4540class Coalesce(Func):
4541    arg_types = {"this": True, "expressions": False}
4542    is_var_len_args = True
4543    _sql_names = ["COALESCE", "IFNULL", "NVL"]
4544
4545
4546class Chr(Func):
4547    arg_types = {"this": True, "charset": False, "expressions": False}
4548    is_var_len_args = True
4549    _sql_names = ["CHR", "CHAR"]
4550
4551
4552class Concat(Func):
4553    arg_types = {"expressions": True, "safe": False, "coalesce": False}
4554    is_var_len_args = True
4555
4556
4557class ConcatWs(Concat):
4558    _sql_names = ["CONCAT_WS"]
4559
4560
4561class Count(AggFunc):
4562    arg_types = {"this": False, "expressions": False}
4563    is_var_len_args = True
4564
4565
4566class CountIf(AggFunc):
4567    _sql_names = ["COUNT_IF", "COUNTIF"]
4568
4569
4570class CurrentDate(Func):
4571    arg_types = {"this": False}
4572
4573
4574class CurrentDatetime(Func):
4575    arg_types = {"this": False}
4576
4577
4578class CurrentTime(Func):
4579    arg_types = {"this": False}
4580
4581
4582class CurrentTimestamp(Func):
4583    arg_types = {"this": False}
4584
4585
4586class CurrentUser(Func):
4587    arg_types = {"this": False}
4588
4589
4590class DateAdd(Func, IntervalOp):
4591    arg_types = {"this": True, "expression": True, "unit": False}
4592
4593
4594class DateSub(Func, IntervalOp):
4595    arg_types = {"this": True, "expression": True, "unit": False}
4596
4597
4598class DateDiff(Func, TimeUnit):
4599    _sql_names = ["DATEDIFF", "DATE_DIFF"]
4600    arg_types = {"this": True, "expression": True, "unit": False}
4601
4602
4603class DateTrunc(Func):
4604    arg_types = {"unit": True, "this": True, "zone": False}
4605
4606    def __init__(self, **args):
4607        unit = args.get("unit")
4608        if isinstance(unit, TimeUnit.VAR_LIKE):
4609            args["unit"] = Literal.string(
4610                (TimeUnit.UNABBREVIATED_UNIT_NAME.get(unit.name) or unit.name).upper()
4611            )
4612        elif isinstance(unit, Week):
4613            unit.set("this", Literal.string(unit.this.name.upper()))
4614
4615        super().__init__(**args)
4616
4617    @property
4618    def unit(self) -> Expression:
4619        return self.args["unit"]
4620
4621
4622class DatetimeAdd(Func, IntervalOp):
4623    arg_types = {"this": True, "expression": True, "unit": False}
4624
4625
4626class DatetimeSub(Func, IntervalOp):
4627    arg_types = {"this": True, "expression": True, "unit": False}
4628
4629
4630class DatetimeDiff(Func, TimeUnit):
4631    arg_types = {"this": True, "expression": True, "unit": False}
4632
4633
4634class DatetimeTrunc(Func, TimeUnit):
4635    arg_types = {"this": True, "unit": True, "zone": False}
4636
4637
4638class DayOfWeek(Func):
4639    _sql_names = ["DAY_OF_WEEK", "DAYOFWEEK"]
4640
4641
4642class DayOfMonth(Func):
4643    _sql_names = ["DAY_OF_MONTH", "DAYOFMONTH"]
4644
4645
4646class DayOfYear(Func):
4647    _sql_names = ["DAY_OF_YEAR", "DAYOFYEAR"]
4648
4649
4650class ToDays(Func):
4651    pass
4652
4653
4654class WeekOfYear(Func):
4655    _sql_names = ["WEEK_OF_YEAR", "WEEKOFYEAR"]
4656
4657
4658class MonthsBetween(Func):
4659    arg_types = {"this": True, "expression": True, "roundoff": False}
4660
4661
4662class LastDay(Func, TimeUnit):
4663    _sql_names = ["LAST_DAY", "LAST_DAY_OF_MONTH"]
4664    arg_types = {"this": True, "unit": False}
4665
4666
4667class Extract(Func):
4668    arg_types = {"this": True, "expression": True}
4669
4670
4671class Timestamp(Func):
4672    arg_types = {"this": False, "expression": False}
4673
4674
4675class TimestampAdd(Func, TimeUnit):
4676    arg_types = {"this": True, "expression": True, "unit": False}
4677
4678
4679class TimestampSub(Func, TimeUnit):
4680    arg_types = {"this": True, "expression": True, "unit": False}
4681
4682
4683class TimestampDiff(Func, TimeUnit):
4684    arg_types = {"this": True, "expression": True, "unit": False}
4685
4686
4687class TimestampTrunc(Func, TimeUnit):
4688    arg_types = {"this": True, "unit": True, "zone": False}
4689
4690
4691class TimeAdd(Func, TimeUnit):
4692    arg_types = {"this": True, "expression": True, "unit": False}
4693
4694
4695class TimeSub(Func, TimeUnit):
4696    arg_types = {"this": True, "expression": True, "unit": False}
4697
4698
4699class TimeDiff(Func, TimeUnit):
4700    arg_types = {"this": True, "expression": True, "unit": False}
4701
4702
4703class TimeTrunc(Func, TimeUnit):
4704    arg_types = {"this": True, "unit": True, "zone": False}
4705
4706
4707class DateFromParts(Func):
4708    _sql_names = ["DATE_FROM_PARTS", "DATEFROMPARTS"]
4709    arg_types = {"year": True, "month": True, "day": True}
4710
4711
4712class TimeFromParts(Func):
4713    _sql_names = ["TIME_FROM_PARTS", "TIMEFROMPARTS"]
4714    arg_types = {
4715        "hour": True,
4716        "min": True,
4717        "sec": True,
4718        "nano": False,
4719        "fractions": False,
4720        "precision": False,
4721    }
4722
4723
4724class DateStrToDate(Func):
4725    pass
4726
4727
4728class DateToDateStr(Func):
4729    pass
4730
4731
4732class DateToDi(Func):
4733    pass
4734
4735
4736# https://cloud.google.com/bigquery/docs/reference/standard-sql/date_functions#date
4737class Date(Func):
4738    arg_types = {"this": False, "zone": False, "expressions": False}
4739    is_var_len_args = True
4740
4741
4742class Day(Func):
4743    pass
4744
4745
4746class Decode(Func):
4747    arg_types = {"this": True, "charset": True, "replace": False}
4748
4749
4750class DiToDate(Func):
4751    pass
4752
4753
4754class Encode(Func):
4755    arg_types = {"this": True, "charset": True}
4756
4757
4758class Exp(Func):
4759    pass
4760
4761
4762# https://docs.snowflake.com/en/sql-reference/functions/flatten
4763class Explode(Func):
4764    arg_types = {"this": True, "expressions": False}
4765    is_var_len_args = True
4766
4767
4768class ExplodeOuter(Explode):
4769    pass
4770
4771
4772class Posexplode(Explode):
4773    pass
4774
4775
4776class PosexplodeOuter(Posexplode):
4777    pass
4778
4779
4780class Floor(Func):
4781    arg_types = {"this": True, "decimals": False}
4782
4783
4784class FromBase64(Func):
4785    pass
4786
4787
4788class ToBase64(Func):
4789    pass
4790
4791
4792class Greatest(Func):
4793    arg_types = {"this": True, "expressions": False}
4794    is_var_len_args = True
4795
4796
4797class GroupConcat(AggFunc):
4798    arg_types = {"this": True, "separator": False}
4799
4800
4801class Hex(Func):
4802    pass
4803
4804
4805class Xor(Connector, Func):
4806    arg_types = {"this": False, "expression": False, "expressions": False}
4807
4808
4809class If(Func):
4810    arg_types = {"this": True, "true": True, "false": False}
4811
4812
4813class Nullif(Func):
4814    arg_types = {"this": True, "expression": True}
4815
4816
4817class Initcap(Func):
4818    arg_types = {"this": True, "expression": False}
4819
4820
4821class IsNan(Func):
4822    _sql_names = ["IS_NAN", "ISNAN"]
4823
4824
4825class IsInf(Func):
4826    _sql_names = ["IS_INF", "ISINF"]
4827
4828
4829class FormatJson(Expression):
4830    pass
4831
4832
4833class JSONKeyValue(Expression):
4834    arg_types = {"this": True, "expression": True}
4835
4836
4837class JSONObject(Func):
4838    arg_types = {
4839        "expressions": False,
4840        "null_handling": False,
4841        "unique_keys": False,
4842        "return_type": False,
4843        "encoding": False,
4844    }
4845
4846
4847class JSONObjectAgg(AggFunc):
4848    arg_types = {
4849        "expressions": False,
4850        "null_handling": False,
4851        "unique_keys": False,
4852        "return_type": False,
4853        "encoding": False,
4854    }
4855
4856
4857# https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/JSON_ARRAY.html
4858class JSONArray(Func):
4859    arg_types = {
4860        "expressions": True,
4861        "null_handling": False,
4862        "return_type": False,
4863        "strict": False,
4864    }
4865
4866
4867# https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/JSON_ARRAYAGG.html
4868class JSONArrayAgg(Func):
4869    arg_types = {
4870        "this": True,
4871        "order": False,
4872        "null_handling": False,
4873        "return_type": False,
4874        "strict": False,
4875    }
4876
4877
4878# https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/JSON_TABLE.html
4879# Note: parsing of JSON column definitions is currently incomplete.
4880class JSONColumnDef(Expression):
4881    arg_types = {"this": False, "kind": False, "path": False, "nested_schema": False}
4882
4883
4884class JSONSchema(Expression):
4885    arg_types = {"expressions": True}
4886
4887
4888# # https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/JSON_TABLE.html
4889class JSONTable(Func):
4890    arg_types = {
4891        "this": True,
4892        "schema": True,
4893        "path": False,
4894        "error_handling": False,
4895        "empty_handling": False,
4896    }
4897
4898
4899class OpenJSONColumnDef(Expression):
4900    arg_types = {"this": True, "kind": True, "path": False, "as_json": False}
4901
4902
4903class OpenJSON(Func):
4904    arg_types = {"this": True, "path": False, "expressions": False}
4905
4906
4907class JSONBContains(Binary):
4908    _sql_names = ["JSONB_CONTAINS"]
4909
4910
4911class JSONExtract(Binary, Func):
4912    _sql_names = ["JSON_EXTRACT"]
4913
4914
4915class JSONExtractScalar(JSONExtract):
4916    _sql_names = ["JSON_EXTRACT_SCALAR"]
4917
4918
4919class JSONBExtract(JSONExtract):
4920    _sql_names = ["JSONB_EXTRACT"]
4921
4922
4923class JSONBExtractScalar(JSONExtract):
4924    _sql_names = ["JSONB_EXTRACT_SCALAR"]
4925
4926
4927class JSONFormat(Func):
4928    arg_types = {"this": False, "options": False}
4929    _sql_names = ["JSON_FORMAT"]
4930
4931
4932# https://dev.mysql.com/doc/refman/8.0/en/json-search-functions.html#operator_member-of
4933class JSONArrayContains(Binary, Predicate, Func):
4934    _sql_names = ["JSON_ARRAY_CONTAINS"]
4935
4936
4937class ParseJSON(Func):
4938    # BigQuery, Snowflake have PARSE_JSON, Presto has JSON_PARSE
4939    _sql_names = ["PARSE_JSON", "JSON_PARSE"]
4940    arg_types = {"this": True, "expressions": False}
4941    is_var_len_args = True
4942
4943
4944# https://docs.snowflake.com/en/sql-reference/functions/get_path
4945class GetPath(Func):
4946    arg_types = {"this": True, "expression": True}
4947
4948    @property
4949    def output_name(self) -> str:
4950        return self.expression.output_name
4951
4952
4953class Least(Func):
4954    arg_types = {"this": True, "expressions": False}
4955    is_var_len_args = True
4956
4957
4958class Left(Func):
4959    arg_types = {"this": True, "expression": True}
4960
4961
4962class Right(Func):
4963    arg_types = {"this": True, "expression": True}
4964
4965
4966class Length(Func):
4967    _sql_names = ["LENGTH", "LEN"]
4968
4969
4970class Levenshtein(Func):
4971    arg_types = {
4972        "this": True,
4973        "expression": False,
4974        "ins_cost": False,
4975        "del_cost": False,
4976        "sub_cost": False,
4977    }
4978
4979
4980class Ln(Func):
4981    pass
4982
4983
4984class Log(Func):
4985    arg_types = {"this": True, "expression": False}
4986
4987
4988class Log2(Func):
4989    pass
4990
4991
4992class Log10(Func):
4993    pass
4994
4995
4996class LogicalOr(AggFunc):
4997    _sql_names = ["LOGICAL_OR", "BOOL_OR", "BOOLOR_AGG"]
4998
4999
5000class LogicalAnd(AggFunc):
5001    _sql_names = ["LOGICAL_AND", "BOOL_AND", "BOOLAND_AGG"]
5002
5003
5004class Lower(Func):
5005    _sql_names = ["LOWER", "LCASE"]
5006
5007
5008class Map(Func):
5009    arg_types = {"keys": False, "values": False}
5010
5011    @property
5012    def keys(self) -> t.List[Expression]:
5013        keys = self.args.get("keys")
5014        return keys.expressions if keys else []
5015
5016    @property
5017    def values(self) -> t.List[Expression]:
5018        values = self.args.get("values")
5019        return values.expressions if values else []
5020
5021
5022class MapFromEntries(Func):
5023    pass
5024
5025
5026class StarMap(Func):
5027    pass
5028
5029
5030class VarMap(Func):
5031    arg_types = {"keys": True, "values": True}
5032    is_var_len_args = True
5033
5034    @property
5035    def keys(self) -> t.List[Expression]:
5036        return self.args["keys"].expressions
5037
5038    @property
5039    def values(self) -> t.List[Expression]:
5040        return self.args["values"].expressions
5041
5042
5043# https://dev.mysql.com/doc/refman/8.0/en/fulltext-search.html
5044class MatchAgainst(Func):
5045    arg_types = {"this": True, "expressions": True, "modifier": False}
5046
5047
5048class Max(AggFunc):
5049    arg_types = {"this": True, "expressions": False}
5050    is_var_len_args = True
5051
5052
5053class MD5(Func):
5054    _sql_names = ["MD5"]
5055
5056
5057# Represents the variant of the MD5 function that returns a binary value
5058class MD5Digest(Func):
5059    _sql_names = ["MD5_DIGEST"]
5060
5061
5062class Min(AggFunc):
5063    arg_types = {"this": True, "expressions": False}
5064    is_var_len_args = True
5065
5066
5067class Month(Func):
5068    pass
5069
5070
5071class Nvl2(Func):
5072    arg_types = {"this": True, "true": True, "false": False}
5073
5074
5075# https://cloud.google.com/bigquery/docs/reference/standard-sql/bigqueryml-syntax-predict#mlpredict_function
5076class Predict(Func):
5077    arg_types = {"this": True, "expression": True, "params_struct": False}
5078
5079
5080class Pow(Binary, Func):
5081    _sql_names = ["POWER", "POW"]
5082
5083
5084class PercentileCont(AggFunc):
5085    arg_types = {"this": True, "expression": False}
5086
5087
5088class PercentileDisc(AggFunc):
5089    arg_types = {"this": True, "expression": False}
5090
5091
5092class Quantile(AggFunc):
5093    arg_types = {"this": True, "quantile": True}
5094
5095
5096class ApproxQuantile(Quantile):
5097    arg_types = {"this": True, "quantile": True, "accuracy": False, "weight": False}
5098
5099
5100class Rand(Func):
5101    _sql_names = ["RAND", "RANDOM"]
5102    arg_types = {"this": False}
5103
5104
5105class Randn(Func):
5106    arg_types = {"this": False}
5107
5108
5109class RangeN(Func):
5110    arg_types = {"this": True, "expressions": True, "each": False}
5111
5112
5113class ReadCSV(Func):
5114    _sql_names = ["READ_CSV"]
5115    is_var_len_args = True
5116    arg_types = {"this": True, "expressions": False}
5117
5118
5119class Reduce(Func):
5120    arg_types = {"this": True, "initial": True, "merge": True, "finish": False}
5121
5122
5123class RegexpExtract(Func):
5124    arg_types = {
5125        "this": True,
5126        "expression": True,
5127        "position": False,
5128        "occurrence": False,
5129        "parameters": False,
5130        "group": False,
5131    }
5132
5133
5134class RegexpReplace(Func):
5135    arg_types = {
5136        "this": True,
5137        "expression": True,
5138        "replacement": False,
5139        "position": False,
5140        "occurrence": False,
5141        "parameters": False,
5142        "modifiers": False,
5143    }
5144
5145
5146class RegexpLike(Binary, Func):
5147    arg_types = {"this": True, "expression": True, "flag": False}
5148
5149
5150class RegexpILike(Binary, Func):
5151    arg_types = {"this": True, "expression": True, "flag": False}
5152
5153
5154# https://spark.apache.org/docs/latest/api/python/reference/pyspark.sql/api/pyspark.sql.functions.split.html
5155# limit is the number of times a pattern is applied
5156class RegexpSplit(Func):
5157    arg_types = {"this": True, "expression": True, "limit": False}
5158
5159
5160class Repeat(Func):
5161    arg_types = {"this": True, "times": True}
5162
5163
5164# https://learn.microsoft.com/en-us/sql/t-sql/functions/round-transact-sql?view=sql-server-ver16
5165# tsql third argument function == trunctaion if not 0
5166class Round(Func):
5167    arg_types = {"this": True, "decimals": False, "truncate": False}
5168
5169
5170class RowNumber(Func):
5171    arg_types: t.Dict[str, t.Any] = {}
5172
5173
5174class SafeDivide(Func):
5175    arg_types = {"this": True, "expression": True}
5176
5177
5178class SHA(Func):
5179    _sql_names = ["SHA", "SHA1"]
5180
5181
5182class SHA2(Func):
5183    _sql_names = ["SHA2"]
5184    arg_types = {"this": True, "length": False}
5185
5186
5187class SortArray(Func):
5188    arg_types = {"this": True, "asc": False}
5189
5190
5191class Split(Func):
5192    arg_types = {"this": True, "expression": True, "limit": False}
5193
5194
5195# Start may be omitted in the case of postgres
5196# https://www.postgresql.org/docs/9.1/functions-string.html @ Table 9-6
5197class Substring(Func):
5198    arg_types = {"this": True, "start": False, "length": False}
5199
5200
5201class StandardHash(Func):
5202    arg_types = {"this": True, "expression": False}
5203
5204
5205class StartsWith(Func):
5206    _sql_names = ["STARTS_WITH", "STARTSWITH"]
5207    arg_types = {"this": True, "expression": True}
5208
5209
5210class StrPosition(Func):
5211    arg_types = {
5212        "this": True,
5213        "substr": True,
5214        "position": False,
5215        "instance": False,
5216    }
5217
5218
5219class StrToDate(Func):
5220    arg_types = {"this": True, "format": True}
5221
5222
5223class StrToTime(Func):
5224    arg_types = {"this": True, "format": True, "zone": False}
5225
5226
5227# Spark allows unix_timestamp()
5228# https://spark.apache.org/docs/3.1.3/api/python/reference/api/pyspark.sql.functions.unix_timestamp.html
5229class StrToUnix(Func):
5230    arg_types = {"this": False, "format": False}
5231
5232
5233# https://prestodb.io/docs/current/functions/string.html
5234# https://spark.apache.org/docs/latest/api/sql/index.html#str_to_map
5235class StrToMap(Func):
5236    arg_types = {
5237        "this": True,
5238        "pair_delim": False,
5239        "key_value_delim": False,
5240        "duplicate_resolution_callback": False,
5241    }
5242
5243
5244class NumberToStr(Func):
5245    arg_types = {"this": True, "format": True, "culture": False}
5246
5247
5248class FromBase(Func):
5249    arg_types = {"this": True, "expression": True}
5250
5251
5252class Struct(Func):
5253    arg_types = {"expressions": False}
5254    is_var_len_args = True
5255
5256
5257class StructExtract(Func):
5258    arg_types = {"this": True, "expression": True}
5259
5260
5261# https://learn.microsoft.com/en-us/sql/t-sql/functions/stuff-transact-sql?view=sql-server-ver16
5262# https://docs.snowflake.com/en/sql-reference/functions/insert
5263class Stuff(Func):
5264    _sql_names = ["STUFF", "INSERT"]
5265    arg_types = {"this": True, "start": True, "length": True, "expression": True}
5266
5267
5268class Sum(AggFunc):
5269    pass
5270
5271
5272class Sqrt(Func):
5273    pass
5274
5275
5276class Stddev(AggFunc):
5277    pass
5278
5279
5280class StddevPop(AggFunc):
5281    pass
5282
5283
5284class StddevSamp(AggFunc):
5285    pass
5286
5287
5288class TimeToStr(Func):
5289    arg_types = {"this": True, "format": True, "culture": False}
5290
5291
5292class TimeToTimeStr(Func):
5293    pass
5294
5295
5296class TimeToUnix(Func):
5297    pass
5298
5299
5300class TimeStrToDate(Func):
5301    pass
5302
5303
5304class TimeStrToTime(Func):
5305    pass
5306
5307
5308class TimeStrToUnix(Func):
5309    pass
5310
5311
5312class Trim(Func):
5313    arg_types = {
5314        "this": True,
5315        "expression": False,
5316        "position": False,
5317        "collation": False,
5318    }
5319
5320
5321class TsOrDsAdd(Func, TimeUnit):
5322    # return_type is used to correctly cast the arguments of this expression when transpiling it
5323    arg_types = {"this": True, "expression": True, "unit": False, "return_type": False}
5324
5325    @property
5326    def return_type(self) -> DataType:
5327        return DataType.build(self.args.get("return_type") or DataType.Type.DATE)
5328
5329
5330class TsOrDsDiff(Func, TimeUnit):
5331    arg_types = {"this": True, "expression": True, "unit": False}
5332
5333
5334class TsOrDsToDateStr(Func):
5335    pass
5336
5337
5338class TsOrDsToDate(Func):
5339    arg_types = {"this": True, "format": False}
5340
5341
5342class TsOrDsToTime(Func):
5343    pass
5344
5345
5346class TsOrDiToDi(Func):
5347    pass
5348
5349
5350class Unhex(Func):
5351    pass
5352
5353
5354# https://cloud.google.com/bigquery/docs/reference/standard-sql/date_functions#unix_date
5355class UnixDate(Func):
5356    pass
5357
5358
5359class UnixToStr(Func):
5360    arg_types = {"this": True, "format": False}
5361
5362
5363# https://prestodb.io/docs/current/functions/datetime.html
5364# presto has weird zone/hours/minutes
5365class UnixToTime(Func):
5366    arg_types = {"this": True, "scale": False, "zone": False, "hours": False, "minutes": False}
5367
5368    SECONDS = Literal.number(0)
5369    DECIS = Literal.number(1)
5370    CENTIS = Literal.number(2)
5371    MILLIS = Literal.number(3)
5372    DECIMILLIS = Literal.number(4)
5373    CENTIMILLIS = Literal.number(5)
5374    MICROS = Literal.number(6)
5375    DECIMICROS = Literal.number(7)
5376    CENTIMICROS = Literal.number(8)
5377    NANOS = Literal.number(9)
5378
5379
5380class UnixToTimeStr(Func):
5381    pass
5382
5383
5384class TimestampFromParts(Func):
5385    _sql_names = ["TIMESTAMP_FROM_PARTS", "TIMESTAMPFROMPARTS"]
5386    arg_types = {
5387        "year": True,
5388        "month": True,
5389        "day": True,
5390        "hour": True,
5391        "min": True,
5392        "sec": True,
5393        "nano": False,
5394        "zone": False,
5395        "milli": False,
5396    }
5397
5398
5399class Upper(Func):
5400    _sql_names = ["UPPER", "UCASE"]
5401
5402
5403class Variance(AggFunc):
5404    _sql_names = ["VARIANCE", "VARIANCE_SAMP", "VAR_SAMP"]
5405
5406
5407class VariancePop(AggFunc):
5408    _sql_names = ["VARIANCE_POP", "VAR_POP"]
5409
5410
5411class Week(Func):
5412    arg_types = {"this": True, "mode": False}
5413
5414
5415class XMLTable(Func):
5416    arg_types = {"this": True, "passing": False, "columns": False, "by_ref": False}
5417
5418
5419class Year(Func):
5420    pass
5421
5422
5423class Use(Expression):
5424    arg_types = {"this": True, "kind": False}
5425
5426
5427class Merge(Expression):
5428    arg_types = {"this": True, "using": True, "on": True, "expressions": True, "with": False}
5429
5430
5431class When(Func):
5432    arg_types = {"matched": True, "source": False, "condition": False, "then": True}
5433
5434
5435# https://docs.oracle.com/javadb/10.8.3.0/ref/rrefsqljnextvaluefor.html
5436# https://learn.microsoft.com/en-us/sql/t-sql/functions/next-value-for-transact-sql?view=sql-server-ver16
5437class NextValueFor(Func):
5438    arg_types = {"this": True, "order": False}
5439
5440
5441def _norm_arg(arg):
5442    return arg.lower() if type(arg) is str else arg
5443
5444
5445ALL_FUNCTIONS = subclasses(__name__, Func, (AggFunc, Anonymous, Func))
5446FUNCTION_BY_NAME = {name: func for func in ALL_FUNCTIONS for name in func.sql_names()}
5447
5448
5449# Helpers
5450@t.overload
5451def maybe_parse(
5452    sql_or_expression: ExpOrStr,
5453    *,
5454    into: t.Type[E],
5455    dialect: DialectType = None,
5456    prefix: t.Optional[str] = None,
5457    copy: bool = False,
5458    **opts,
5459) -> E:
5460    ...
5461
5462
5463@t.overload
5464def maybe_parse(
5465    sql_or_expression: str | E,
5466    *,
5467    into: t.Optional[IntoType] = None,
5468    dialect: DialectType = None,
5469    prefix: t.Optional[str] = None,
5470    copy: bool = False,
5471    **opts,
5472) -> E:
5473    ...
5474
5475
5476def maybe_parse(
5477    sql_or_expression: ExpOrStr,
5478    *,
5479    into: t.Optional[IntoType] = None,
5480    dialect: DialectType = None,
5481    prefix: t.Optional[str] = None,
5482    copy: bool = False,
5483    **opts,
5484) -> Expression:
5485    """Gracefully handle a possible string or expression.
5486
5487    Example:
5488        >>> maybe_parse("1")
5489        Literal(this=1, is_string=False)
5490        >>> maybe_parse(to_identifier("x"))
5491        Identifier(this=x, quoted=False)
5492
5493    Args:
5494        sql_or_expression: the SQL code string or an expression
5495        into: the SQLGlot Expression to parse into
5496        dialect: the dialect used to parse the input expressions (in the case that an
5497            input expression is a SQL string).
5498        prefix: a string to prefix the sql with before it gets parsed
5499            (automatically includes a space)
5500        copy: whether or not to copy the expression.
5501        **opts: other options to use to parse the input expressions (again, in the case
5502            that an input expression is a SQL string).
5503
5504    Returns:
5505        Expression: the parsed or given expression.
5506    """
5507    if isinstance(sql_or_expression, Expression):
5508        if copy:
5509            return sql_or_expression.copy()
5510        return sql_or_expression
5511
5512    if sql_or_expression is None:
5513        raise ParseError(f"SQL cannot be None")
5514
5515    import sqlglot
5516
5517    sql = str(sql_or_expression)
5518    if prefix:
5519        sql = f"{prefix} {sql}"
5520
5521    return sqlglot.parse_one(sql, read=dialect, into=into, **opts)
5522
5523
5524@t.overload
5525def maybe_copy(instance: None, copy: bool = True) -> None:
5526    ...
5527
5528
5529@t.overload
5530def maybe_copy(instance: E, copy: bool = True) -> E:
5531    ...
5532
5533
5534def maybe_copy(instance, copy=True):
5535    return instance.copy() if copy and instance else instance
5536
5537
5538def _to_s(node: t.Any, verbose: bool = False, level: int = 0) -> str:
5539    """Generate a textual representation of an Expression tree"""
5540    indent = "\n" + ("  " * (level + 1))
5541    delim = f",{indent}"
5542
5543    if isinstance(node, Expression):
5544        args = {k: v for k, v in node.args.items() if (v is not None and v != []) or verbose}
5545
5546        if (node.type or verbose) and not isinstance(node, DataType):
5547            args["_type"] = node.type
5548        if node.comments or verbose:
5549            args["_comments"] = node.comments
5550
5551        if verbose:
5552            args["_id"] = id(node)
5553
5554        # Inline leaves for a more compact representation
5555        if node.is_leaf():
5556            indent = ""
5557            delim = ", "
5558
5559        items = delim.join([f"{k}={_to_s(v, verbose, level + 1)}" for k, v in args.items()])
5560        return f"{node.__class__.__name__}({indent}{items})"
5561
5562    if isinstance(node, list):
5563        items = delim.join(_to_s(i, verbose, level + 1) for i in node)
5564        items = f"{indent}{items}" if items else ""
5565        return f"[{items}]"
5566
5567    # Indent multiline strings to match the current level
5568    return indent.join(textwrap.dedent(str(node).strip("\n")).splitlines())
5569
5570
5571def _is_wrong_expression(expression, into):
5572    return isinstance(expression, Expression) and not isinstance(expression, into)
5573
5574
5575def _apply_builder(
5576    expression,
5577    instance,
5578    arg,
5579    copy=True,
5580    prefix=None,
5581    into=None,
5582    dialect=None,
5583    into_arg="this",
5584    **opts,
5585):
5586    if _is_wrong_expression(expression, into):
5587        expression = into(**{into_arg: expression})
5588    instance = maybe_copy(instance, copy)
5589    expression = maybe_parse(
5590        sql_or_expression=expression,
5591        prefix=prefix,
5592        into=into,
5593        dialect=dialect,
5594        **opts,
5595    )
5596    instance.set(arg, expression)
5597    return instance
5598
5599
5600def _apply_child_list_builder(
5601    *expressions,
5602    instance,
5603    arg,
5604    append=True,
5605    copy=True,
5606    prefix=None,
5607    into=None,
5608    dialect=None,
5609    properties=None,
5610    **opts,
5611):
5612    instance = maybe_copy(instance, copy)
5613    parsed = []
5614    for expression in expressions:
5615        if expression is not None:
5616            if _is_wrong_expression(expression, into):
5617                expression = into(expressions=[expression])
5618
5619            expression = maybe_parse(
5620                expression,
5621                into=into,
5622                dialect=dialect,
5623                prefix=prefix,
5624                **opts,
5625            )
5626            parsed.extend(expression.expressions)
5627
5628    existing = instance.args.get(arg)
5629    if append and existing:
5630        parsed = existing.expressions + parsed
5631
5632    child = into(expressions=parsed)
5633    for k, v in (properties or {}).items():
5634        child.set(k, v)
5635    instance.set(arg, child)
5636
5637    return instance
5638
5639
5640def _apply_list_builder(
5641    *expressions,
5642    instance,
5643    arg,
5644    append=True,
5645    copy=True,
5646    prefix=None,
5647    into=None,
5648    dialect=None,
5649    **opts,
5650):
5651    inst = maybe_copy(instance, copy)
5652
5653    expressions = [
5654        maybe_parse(
5655            sql_or_expression=expression,
5656            into=into,
5657            prefix=prefix,
5658            dialect=dialect,
5659            **opts,
5660        )
5661        for expression in expressions
5662        if expression is not None
5663    ]
5664
5665    existing_expressions = inst.args.get(arg)
5666    if append and existing_expressions:
5667        expressions = existing_expressions + expressions
5668
5669    inst.set(arg, expressions)
5670    return inst
5671
5672
5673def _apply_conjunction_builder(
5674    *expressions,
5675    instance,
5676    arg,
5677    into=None,
5678    append=True,
5679    copy=True,
5680    dialect=None,
5681    **opts,
5682):
5683    expressions = [exp for exp in expressions if exp is not None and exp != ""]
5684    if not expressions:
5685        return instance
5686
5687    inst = maybe_copy(instance, copy)
5688
5689    existing = inst.args.get(arg)
5690    if append and existing is not None:
5691        expressions = [existing.this if into else existing] + list(expressions)
5692
5693    node = and_(*expressions, dialect=dialect, copy=copy, **opts)
5694
5695    inst.set(arg, into(this=node) if into else node)
5696    return inst
5697
5698
5699def _apply_cte_builder(
5700    instance: E,
5701    alias: ExpOrStr,
5702    as_: ExpOrStr,
5703    recursive: t.Optional[bool] = None,
5704    append: bool = True,
5705    dialect: DialectType = None,
5706    copy: bool = True,
5707    **opts,
5708) -> E:
5709    alias_expression = maybe_parse(alias, dialect=dialect, into=TableAlias, **opts)
5710    as_expression = maybe_parse(as_, dialect=dialect, **opts)
5711    cte = CTE(this=as_expression, alias=alias_expression)
5712    return _apply_child_list_builder(
5713        cte,
5714        instance=instance,
5715        arg="with",
5716        append=append,
5717        copy=copy,
5718        into=With,
5719        properties={"recursive": recursive or False},
5720    )
5721
5722
5723def _combine(
5724    expressions: t.Sequence[t.Optional[ExpOrStr]],
5725    operator: t.Type[Connector],
5726    dialect: DialectType = None,
5727    copy: bool = True,
5728    **opts,
5729) -> Expression:
5730    conditions = [
5731        condition(expression, dialect=dialect, copy=copy, **opts)
5732        for expression in expressions
5733        if expression is not None
5734    ]
5735
5736    this, *rest = conditions
5737    if rest:
5738        this = _wrap(this, Connector)
5739    for expression in rest:
5740        this = operator(this=this, expression=_wrap(expression, Connector))
5741
5742    return this
5743
5744
5745def _wrap(expression: E, kind: t.Type[Expression]) -> E | Paren:
5746    return Paren(this=expression) if isinstance(expression, kind) else expression
5747
5748
5749def union(
5750    left: ExpOrStr,
5751    right: ExpOrStr,
5752    distinct: bool = True,
5753    dialect: DialectType = None,
5754    copy: bool = True,
5755    **opts,
5756) -> Union:
5757    """
5758    Initializes a syntax tree from one UNION expression.
5759
5760    Example:
5761        >>> union("SELECT * FROM foo", "SELECT * FROM bla").sql()
5762        'SELECT * FROM foo UNION SELECT * FROM bla'
5763
5764    Args:
5765        left: the SQL code string corresponding to the left-hand side.
5766            If an `Expression` instance is passed, it will be used as-is.
5767        right: the SQL code string corresponding to the right-hand side.
5768            If an `Expression` instance is passed, it will be used as-is.
5769        distinct: set the DISTINCT flag if and only if this is true.
5770        dialect: the dialect used to parse the input expression.
5771        copy: whether or not to copy the expression.
5772        opts: other options to use to parse the input expressions.
5773
5774    Returns:
5775        The new Union instance.
5776    """
5777    left = maybe_parse(sql_or_expression=left, dialect=dialect, copy=copy, **opts)
5778    right = maybe_parse(sql_or_expression=right, dialect=dialect, copy=copy, **opts)
5779
5780    return Union(this=left, expression=right, distinct=distinct)
5781
5782
5783def intersect(
5784    left: ExpOrStr,
5785    right: ExpOrStr,
5786    distinct: bool = True,
5787    dialect: DialectType = None,
5788    copy: bool = True,
5789    **opts,
5790) -> Intersect:
5791    """
5792    Initializes a syntax tree from one INTERSECT expression.
5793
5794    Example:
5795        >>> intersect("SELECT * FROM foo", "SELECT * FROM bla").sql()
5796        'SELECT * FROM foo INTERSECT SELECT * FROM bla'
5797
5798    Args:
5799        left: the SQL code string corresponding to the left-hand side.
5800            If an `Expression` instance is passed, it will be used as-is.
5801        right: the SQL code string corresponding to the right-hand side.
5802            If an `Expression` instance is passed, it will be used as-is.
5803        distinct: set the DISTINCT flag if and only if this is true.
5804        dialect: the dialect used to parse the input expression.
5805        copy: whether or not to copy the expression.
5806        opts: other options to use to parse the input expressions.
5807
5808    Returns:
5809        The new Intersect instance.
5810    """
5811    left = maybe_parse(sql_or_expression=left, dialect=dialect, copy=copy, **opts)
5812    right = maybe_parse(sql_or_expression=right, dialect=dialect, copy=copy, **opts)
5813
5814    return Intersect(this=left, expression=right, distinct=distinct)
5815
5816
5817def except_(
5818    left: ExpOrStr,
5819    right: ExpOrStr,
5820    distinct: bool = True,
5821    dialect: DialectType = None,
5822    copy: bool = True,
5823    **opts,
5824) -> Except:
5825    """
5826    Initializes a syntax tree from one EXCEPT expression.
5827
5828    Example:
5829        >>> except_("SELECT * FROM foo", "SELECT * FROM bla").sql()
5830        'SELECT * FROM foo EXCEPT SELECT * FROM bla'
5831
5832    Args:
5833        left: the SQL code string corresponding to the left-hand side.
5834            If an `Expression` instance is passed, it will be used as-is.
5835        right: the SQL code string corresponding to the right-hand side.
5836            If an `Expression` instance is passed, it will be used as-is.
5837        distinct: set the DISTINCT flag if and only if this is true.
5838        dialect: the dialect used to parse the input expression.
5839        copy: whether or not to copy the expression.
5840        opts: other options to use to parse the input expressions.
5841
5842    Returns:
5843        The new Except instance.
5844    """
5845    left = maybe_parse(sql_or_expression=left, dialect=dialect, copy=copy, **opts)
5846    right = maybe_parse(sql_or_expression=right, dialect=dialect, copy=copy, **opts)
5847
5848    return Except(this=left, expression=right, distinct=distinct)
5849
5850
5851def select(*expressions: ExpOrStr, dialect: DialectType = None, **opts) -> Select:
5852    """
5853    Initializes a syntax tree from one or multiple SELECT expressions.
5854
5855    Example:
5856        >>> select("col1", "col2").from_("tbl").sql()
5857        'SELECT col1, col2 FROM tbl'
5858
5859    Args:
5860        *expressions: the SQL code string to parse as the expressions of a
5861            SELECT statement. If an Expression instance is passed, this is used as-is.
5862        dialect: the dialect used to parse the input expressions (in the case that an
5863            input expression is a SQL string).
5864        **opts: other options to use to parse the input expressions (again, in the case
5865            that an input expression is a SQL string).
5866
5867    Returns:
5868        Select: the syntax tree for the SELECT statement.
5869    """
5870    return Select().select(*expressions, dialect=dialect, **opts)
5871
5872
5873def from_(expression: ExpOrStr, dialect: DialectType = None, **opts) -> Select:
5874    """
5875    Initializes a syntax tree from a FROM expression.
5876
5877    Example:
5878        >>> from_("tbl").select("col1", "col2").sql()
5879        'SELECT col1, col2 FROM tbl'
5880
5881    Args:
5882        *expression: the SQL code string to parse as the FROM expressions of a
5883            SELECT statement. If an Expression instance is passed, this is used as-is.
5884        dialect: the dialect used to parse the input expression (in the case that the
5885            input expression is a SQL string).
5886        **opts: other options to use to parse the input expressions (again, in the case
5887            that the input expression is a SQL string).
5888
5889    Returns:
5890        Select: the syntax tree for the SELECT statement.
5891    """
5892    return Select().from_(expression, dialect=dialect, **opts)
5893
5894
5895def update(
5896    table: str | Table,
5897    properties: dict,
5898    where: t.Optional[ExpOrStr] = None,
5899    from_: t.Optional[ExpOrStr] = None,
5900    dialect: DialectType = None,
5901    **opts,
5902) -> Update:
5903    """
5904    Creates an update statement.
5905
5906    Example:
5907        >>> update("my_table", {"x": 1, "y": "2", "z": None}, from_="baz", where="id > 1").sql()
5908        "UPDATE my_table SET x = 1, y = '2', z = NULL FROM baz WHERE id > 1"
5909
5910    Args:
5911        *properties: dictionary of properties to set which are
5912            auto converted to sql objects eg None -> NULL
5913        where: sql conditional parsed into a WHERE statement
5914        from_: sql statement parsed into a FROM statement
5915        dialect: the dialect used to parse the input expressions.
5916        **opts: other options to use to parse the input expressions.
5917
5918    Returns:
5919        Update: the syntax tree for the UPDATE statement.
5920    """
5921    update_expr = Update(this=maybe_parse(table, into=Table, dialect=dialect))
5922    update_expr.set(
5923        "expressions",
5924        [
5925            EQ(this=maybe_parse(k, dialect=dialect, **opts), expression=convert(v))
5926            for k, v in properties.items()
5927        ],
5928    )
5929    if from_:
5930        update_expr.set(
5931            "from",
5932            maybe_parse(from_, into=From, dialect=dialect, prefix="FROM", **opts),
5933        )
5934    if isinstance(where, Condition):
5935        where = Where(this=where)
5936    if where:
5937        update_expr.set(
5938            "where",
5939            maybe_parse(where, into=Where, dialect=dialect, prefix="WHERE", **opts),
5940        )
5941    return update_expr
5942
5943
5944def delete(
5945    table: ExpOrStr,
5946    where: t.Optional[ExpOrStr] = None,
5947    returning: t.Optional[ExpOrStr] = None,
5948    dialect: DialectType = None,
5949    **opts,
5950) -> Delete:
5951    """
5952    Builds a delete statement.
5953
5954    Example:
5955        >>> delete("my_table", where="id > 1").sql()
5956        'DELETE FROM my_table WHERE id > 1'
5957
5958    Args:
5959        where: sql conditional parsed into a WHERE statement
5960        returning: sql conditional parsed into a RETURNING statement
5961        dialect: the dialect used to parse the input expressions.
5962        **opts: other options to use to parse the input expressions.
5963
5964    Returns:
5965        Delete: the syntax tree for the DELETE statement.
5966    """
5967    delete_expr = Delete().delete(table, dialect=dialect, copy=False, **opts)
5968    if where:
5969        delete_expr = delete_expr.where(where, dialect=dialect, copy=False, **opts)
5970    if returning:
5971        delete_expr = t.cast(
5972            Delete, delete_expr.returning(returning, dialect=dialect, copy=False, **opts)
5973        )
5974    return delete_expr
5975
5976
5977def insert(
5978    expression: ExpOrStr,
5979    into: ExpOrStr,
5980    columns: t.Optional[t.Sequence[str | Identifier]] = None,
5981    overwrite: t.Optional[bool] = None,
5982    returning: t.Optional[ExpOrStr] = None,
5983    dialect: DialectType = None,
5984    copy: bool = True,
5985    **opts,
5986) -> Insert:
5987    """
5988    Builds an INSERT statement.
5989
5990    Example:
5991        >>> insert("VALUES (1, 2, 3)", "tbl").sql()
5992        'INSERT INTO tbl VALUES (1, 2, 3)'
5993
5994    Args:
5995        expression: the sql string or expression of the INSERT statement
5996        into: the tbl to insert data to.
5997        columns: optionally the table's column names.
5998        overwrite: whether to INSERT OVERWRITE or not.
5999        returning: sql conditional parsed into a RETURNING statement
6000        dialect: the dialect used to parse the input expressions.
6001        copy: whether or not to copy the expression.
6002        **opts: other options to use to parse the input expressions.
6003
6004    Returns:
6005        Insert: the syntax tree for the INSERT statement.
6006    """
6007    expr = maybe_parse(expression, dialect=dialect, copy=copy, **opts)
6008    this: Table | Schema = maybe_parse(into, into=Table, dialect=dialect, copy=copy, **opts)
6009
6010    if columns:
6011        this = Schema(this=this, expressions=[to_identifier(c, copy=copy) for c in columns])
6012
6013    insert = Insert(this=this, expression=expr, overwrite=overwrite)
6014
6015    if returning:
6016        insert = t.cast(Insert, insert.returning(returning, dialect=dialect, copy=False, **opts))
6017
6018    return insert
6019
6020
6021def condition(
6022    expression: ExpOrStr, dialect: DialectType = None, copy: bool = True, **opts
6023) -> Condition:
6024    """
6025    Initialize a logical condition expression.
6026
6027    Example:
6028        >>> condition("x=1").sql()
6029        'x = 1'
6030
6031        This is helpful for composing larger logical syntax trees:
6032        >>> where = condition("x=1")
6033        >>> where = where.and_("y=1")
6034        >>> Select().from_("tbl").select("*").where(where).sql()
6035        'SELECT * FROM tbl WHERE x = 1 AND y = 1'
6036
6037    Args:
6038        *expression: the SQL code string to parse.
6039            If an Expression instance is passed, this is used as-is.
6040        dialect: the dialect used to parse the input expression (in the case that the
6041            input expression is a SQL string).
6042        copy: Whether or not to copy `expression` (only applies to expressions).
6043        **opts: other options to use to parse the input expressions (again, in the case
6044            that the input expression is a SQL string).
6045
6046    Returns:
6047        The new Condition instance
6048    """
6049    return maybe_parse(
6050        expression,
6051        into=Condition,
6052        dialect=dialect,
6053        copy=copy,
6054        **opts,
6055    )
6056
6057
6058def and_(
6059    *expressions: t.Optional[ExpOrStr], dialect: DialectType = None, copy: bool = True, **opts
6060) -> Condition:
6061    """
6062    Combine multiple conditions with an AND logical operator.
6063
6064    Example:
6065        >>> and_("x=1", and_("y=1", "z=1")).sql()
6066        'x = 1 AND (y = 1 AND z = 1)'
6067
6068    Args:
6069        *expressions: the SQL code strings to parse.
6070            If an Expression instance is passed, this is used as-is.
6071        dialect: the dialect used to parse the input expression.
6072        copy: whether or not to copy `expressions` (only applies to Expressions).
6073        **opts: other options to use to parse the input expressions.
6074
6075    Returns:
6076        And: the new condition
6077    """
6078    return t.cast(Condition, _combine(expressions, And, dialect, copy=copy, **opts))
6079
6080
6081def or_(
6082    *expressions: t.Optional[ExpOrStr], dialect: DialectType = None, copy: bool = True, **opts
6083) -> Condition:
6084    """
6085    Combine multiple conditions with an OR logical operator.
6086
6087    Example:
6088        >>> or_("x=1", or_("y=1", "z=1")).sql()
6089        'x = 1 OR (y = 1 OR z = 1)'
6090
6091    Args:
6092        *expressions: the SQL code strings to parse.
6093            If an Expression instance is passed, this is used as-is.
6094        dialect: the dialect used to parse the input expression.
6095        copy: whether or not to copy `expressions` (only applies to Expressions).
6096        **opts: other options to use to parse the input expressions.
6097
6098    Returns:
6099        Or: the new condition
6100    """
6101    return t.cast(Condition, _combine(expressions, Or, dialect, copy=copy, **opts))
6102
6103
6104def not_(expression: ExpOrStr, dialect: DialectType = None, copy: bool = True, **opts) -> Not:
6105    """
6106    Wrap a condition with a NOT operator.
6107
6108    Example:
6109        >>> not_("this_suit='black'").sql()
6110        "NOT this_suit = 'black'"
6111
6112    Args:
6113        expression: the SQL code string to parse.
6114            If an Expression instance is passed, this is used as-is.
6115        dialect: the dialect used to parse the input expression.
6116        copy: whether to copy the expression or not.
6117        **opts: other options to use to parse the input expressions.
6118
6119    Returns:
6120        The new condition.
6121    """
6122    this = condition(
6123        expression,
6124        dialect=dialect,
6125        copy=copy,
6126        **opts,
6127    )
6128    return Not(this=_wrap(this, Connector))
6129
6130
6131def paren(expression: ExpOrStr, copy: bool = True) -> Paren:
6132    """
6133    Wrap an expression in parentheses.
6134
6135    Example:
6136        >>> paren("5 + 3").sql()
6137        '(5 + 3)'
6138
6139    Args:
6140        expression: the SQL code string to parse.
6141            If an Expression instance is passed, this is used as-is.
6142        copy: whether to copy the expression or not.
6143
6144    Returns:
6145        The wrapped expression.
6146    """
6147    return Paren(this=maybe_parse(expression, copy=copy))
6148
6149
6150SAFE_IDENTIFIER_RE = re.compile(r"^[_a-zA-Z][\w]*$")
6151
6152
6153@t.overload
6154def to_identifier(name: None, quoted: t.Optional[bool] = None, copy: bool = True) -> None:
6155    ...
6156
6157
6158@t.overload
6159def to_identifier(
6160    name: str | Identifier, quoted: t.Optional[bool] = None, copy: bool = True
6161) -> Identifier:
6162    ...
6163
6164
6165def to_identifier(name, quoted=None, copy=True):
6166    """Builds an identifier.
6167
6168    Args:
6169        name: The name to turn into an identifier.
6170        quoted: Whether or not force quote the identifier.
6171        copy: Whether or not to copy name if it's an Identifier.
6172
6173    Returns:
6174        The identifier ast node.
6175    """
6176
6177    if name is None:
6178        return None
6179
6180    if isinstance(name, Identifier):
6181        identifier = maybe_copy(name, copy)
6182    elif isinstance(name, str):
6183        identifier = Identifier(
6184            this=name,
6185            quoted=not SAFE_IDENTIFIER_RE.match(name) if quoted is None else quoted,
6186        )
6187    else:
6188        raise ValueError(f"Name needs to be a string or an Identifier, got: {name.__class__}")
6189    return identifier
6190
6191
6192def parse_identifier(name: str | Identifier, dialect: DialectType = None) -> Identifier:
6193    """
6194    Parses a given string into an identifier.
6195
6196    Args:
6197        name: The name to parse into an identifier.
6198        dialect: The dialect to parse against.
6199
6200    Returns:
6201        The identifier ast node.
6202    """
6203    try:
6204        expression = maybe_parse(name, dialect=dialect, into=Identifier)
6205    except ParseError:
6206        expression = to_identifier(name)
6207
6208    return expression
6209
6210
6211INTERVAL_STRING_RE = re.compile(r"\s*([0-9]+)\s*([a-zA-Z]+)\s*")
6212
6213
6214def to_interval(interval: str | Literal) -> Interval:
6215    """Builds an interval expression from a string like '1 day' or '5 months'."""
6216    if isinstance(interval, Literal):
6217        if not interval.is_string:
6218            raise ValueError("Invalid interval string.")
6219
6220        interval = interval.this
6221
6222    interval_parts = INTERVAL_STRING_RE.match(interval)  # type: ignore
6223
6224    if not interval_parts:
6225        raise ValueError("Invalid interval string.")
6226
6227    return Interval(
6228        this=Literal.string(interval_parts.group(1)),
6229        unit=Var(this=interval_parts.group(2).upper()),
6230    )
6231
6232
6233@t.overload
6234def to_table(sql_path: str | Table, **kwargs) -> Table:
6235    ...
6236
6237
6238@t.overload
6239def to_table(sql_path: None, **kwargs) -> None:
6240    ...
6241
6242
6243def to_table(
6244    sql_path: t.Optional[str | Table], dialect: DialectType = None, copy: bool = True, **kwargs
6245) -> t.Optional[Table]:
6246    """
6247    Create a table expression from a `[catalog].[schema].[table]` sql path. Catalog and schema are optional.
6248    If a table is passed in then that table is returned.
6249
6250    Args:
6251        sql_path: a `[catalog].[schema].[table]` string.
6252        dialect: the source dialect according to which the table name will be parsed.
6253        copy: Whether or not to copy a table if it is passed in.
6254        kwargs: the kwargs to instantiate the resulting `Table` expression with.
6255
6256    Returns:
6257        A table expression.
6258    """
6259    if sql_path is None or isinstance(sql_path, Table):
6260        return maybe_copy(sql_path, copy=copy)
6261    if not isinstance(sql_path, str):
6262        raise ValueError(f"Invalid type provided for a table: {type(sql_path)}")
6263
6264    table = maybe_parse(sql_path, into=Table, dialect=dialect)
6265    if table:
6266        for k, v in kwargs.items():
6267            table.set(k, v)
6268
6269    return table
6270
6271
6272def to_column(sql_path: str | Column, **kwargs) -> Column:
6273    """
6274    Create a column from a `[table].[column]` sql path. Schema is optional.
6275
6276    If a column is passed in then that column is returned.
6277
6278    Args:
6279        sql_path: `[table].[column]` string
6280    Returns:
6281        Table: A column expression
6282    """
6283    if sql_path is None or isinstance(sql_path, Column):
6284        return sql_path
6285    if not isinstance(sql_path, str):
6286        raise ValueError(f"Invalid type provided for column: {type(sql_path)}")
6287    return column(*reversed(sql_path.split(".")), **kwargs)  # type: ignore
6288
6289
6290def alias_(
6291    expression: ExpOrStr,
6292    alias: str | Identifier,
6293    table: bool | t.Sequence[str | Identifier] = False,
6294    quoted: t.Optional[bool] = None,
6295    dialect: DialectType = None,
6296    copy: bool = True,
6297    **opts,
6298):
6299    """Create an Alias expression.
6300
6301    Example:
6302        >>> alias_('foo', 'bar').sql()
6303        'foo AS bar'
6304
6305        >>> alias_('(select 1, 2)', 'bar', table=['a', 'b']).sql()
6306        '(SELECT 1, 2) AS bar(a, b)'
6307
6308    Args:
6309        expression: the SQL code strings to parse.
6310            If an Expression instance is passed, this is used as-is.
6311        alias: the alias name to use. If the name has
6312            special characters it is quoted.
6313        table: Whether or not to create a table alias, can also be a list of columns.
6314        quoted: whether or not to quote the alias
6315        dialect: the dialect used to parse the input expression.
6316        copy: Whether or not to copy the expression.
6317        **opts: other options to use to parse the input expressions.
6318
6319    Returns:
6320        Alias: the aliased expression
6321    """
6322    exp = maybe_parse(expression, dialect=dialect, copy=copy, **opts)
6323    alias = to_identifier(alias, quoted=quoted)
6324
6325    if table:
6326        table_alias = TableAlias(this=alias)
6327        exp.set("alias", table_alias)
6328
6329        if not isinstance(table, bool):
6330            for column in table:
6331                table_alias.append("columns", to_identifier(column, quoted=quoted))
6332
6333        return exp
6334
6335    # We don't set the "alias" arg for Window expressions, because that would add an IDENTIFIER node in
6336    # the AST, representing a "named_window" [1] construct (eg. bigquery). What we want is an ALIAS node
6337    # for the complete Window expression.
6338    #
6339    # [1]: https://cloud.google.com/bigquery/docs/reference/standard-sql/window-function-calls
6340
6341    if "alias" in exp.arg_types and not isinstance(exp, Window):
6342        exp.set("alias", alias)
6343        return exp
6344    return Alias(this=exp, alias=alias)
6345
6346
6347def subquery(
6348    expression: ExpOrStr,
6349    alias: t.Optional[Identifier | str] = None,
6350    dialect: DialectType = None,
6351    **opts,
6352) -> Select:
6353    """
6354    Build a subquery expression.
6355
6356    Example:
6357        >>> subquery('select x from tbl', 'bar').select('x').sql()
6358        'SELECT x FROM (SELECT x FROM tbl) AS bar'
6359
6360    Args:
6361        expression: the SQL code strings to parse.
6362            If an Expression instance is passed, this is used as-is.
6363        alias: the alias name to use.
6364        dialect: the dialect used to parse the input expression.
6365        **opts: other options to use to parse the input expressions.
6366
6367    Returns:
6368        A new Select instance with the subquery expression included.
6369    """
6370
6371    expression = maybe_parse(expression, dialect=dialect, **opts).subquery(alias)
6372    return Select().from_(expression, dialect=dialect, **opts)
6373
6374
6375@t.overload
6376def column(
6377    col: str | Identifier,
6378    table: t.Optional[str | Identifier] = None,
6379    db: t.Optional[str | Identifier] = None,
6380    catalog: t.Optional[str | Identifier] = None,
6381    *,
6382    fields: t.Collection[t.Union[str, Identifier]],
6383    quoted: t.Optional[bool] = None,
6384    copy: bool = True,
6385) -> Dot:
6386    pass
6387
6388
6389@t.overload
6390def column(
6391    col: str | Identifier,
6392    table: t.Optional[str | Identifier] = None,
6393    db: t.Optional[str | Identifier] = None,
6394    catalog: t.Optional[str | Identifier] = None,
6395    *,
6396    fields: Lit[None] = None,
6397    quoted: t.Optional[bool] = None,
6398    copy: bool = True,
6399) -> Column:
6400    pass
6401
6402
6403def column(
6404    col,
6405    table=None,
6406    db=None,
6407    catalog=None,
6408    *,
6409    fields=None,
6410    quoted=None,
6411    copy=True,
6412):
6413    """
6414    Build a Column.
6415
6416    Args:
6417        col: Column name.
6418        table: Table name.
6419        db: Database name.
6420        catalog: Catalog name.
6421        fields: Additional fields using dots.
6422        quoted: Whether to force quotes on the column's identifiers.
6423        copy: Whether or not to copy identifiers if passed in.
6424
6425    Returns:
6426        The new Column instance.
6427    """
6428    this = Column(
6429        this=to_identifier(col, quoted=quoted, copy=copy),
6430        table=to_identifier(table, quoted=quoted, copy=copy),
6431        db=to_identifier(db, quoted=quoted, copy=copy),
6432        catalog=to_identifier(catalog, quoted=quoted, copy=copy),
6433    )
6434
6435    if fields:
6436        this = Dot.build((this, *(to_identifier(field, copy=copy) for field in fields)))
6437    return this
6438
6439
6440def cast(expression: ExpOrStr, to: DATA_TYPE, **opts) -> Cast:
6441    """Cast an expression to a data type.
6442
6443    Example:
6444        >>> cast('x + 1', 'int').sql()
6445        'CAST(x + 1 AS INT)'
6446
6447    Args:
6448        expression: The expression to cast.
6449        to: The datatype to cast to.
6450
6451    Returns:
6452        The new Cast instance.
6453    """
6454    expression = maybe_parse(expression, **opts)
6455    data_type = DataType.build(to, **opts)
6456    expression = Cast(this=expression, to=data_type)
6457    expression.type = data_type
6458    return expression
6459
6460
6461def table_(
6462    table: Identifier | str,
6463    db: t.Optional[Identifier | str] = None,
6464    catalog: t.Optional[Identifier | str] = None,
6465    quoted: t.Optional[bool] = None,
6466    alias: t.Optional[Identifier | str] = None,
6467) -> Table:
6468    """Build a Table.
6469
6470    Args:
6471        table: Table name.
6472        db: Database name.
6473        catalog: Catalog name.
6474        quote: Whether to force quotes on the table's identifiers.
6475        alias: Table's alias.
6476
6477    Returns:
6478        The new Table instance.
6479    """
6480    return Table(
6481        this=to_identifier(table, quoted=quoted) if table else None,
6482        db=to_identifier(db, quoted=quoted) if db else None,
6483        catalog=to_identifier(catalog, quoted=quoted) if catalog else None,
6484        alias=TableAlias(this=to_identifier(alias)) if alias else None,
6485    )
6486
6487
6488def values(
6489    values: t.Iterable[t.Tuple[t.Any, ...]],
6490    alias: t.Optional[str] = None,
6491    columns: t.Optional[t.Iterable[str] | t.Dict[str, DataType]] = None,
6492) -> Values:
6493    """Build VALUES statement.
6494
6495    Example:
6496        >>> values([(1, '2')]).sql()
6497        "VALUES (1, '2')"
6498
6499    Args:
6500        values: values statements that will be converted to SQL
6501        alias: optional alias
6502        columns: Optional list of ordered column names or ordered dictionary of column names to types.
6503         If either are provided then an alias is also required.
6504
6505    Returns:
6506        Values: the Values expression object
6507    """
6508    if columns and not alias:
6509        raise ValueError("Alias is required when providing columns")
6510
6511    return Values(
6512        expressions=[convert(tup) for tup in values],
6513        alias=(
6514            TableAlias(this=to_identifier(alias), columns=[to_identifier(x) for x in columns])
6515            if columns
6516            else (TableAlias(this=to_identifier(alias)) if alias else None)
6517        ),
6518    )
6519
6520
6521def var(name: t.Optional[ExpOrStr]) -> Var:
6522    """Build a SQL variable.
6523
6524    Example:
6525        >>> repr(var('x'))
6526        'Var(this=x)'
6527
6528        >>> repr(var(column('x', table='y')))
6529        'Var(this=x)'
6530
6531    Args:
6532        name: The name of the var or an expression who's name will become the var.
6533
6534    Returns:
6535        The new variable node.
6536    """
6537    if not name:
6538        raise ValueError("Cannot convert empty name into var.")
6539
6540    if isinstance(name, Expression):
6541        name = name.name
6542    return Var(this=name)
6543
6544
6545def rename_table(old_name: str | Table, new_name: str | Table) -> AlterTable:
6546    """Build ALTER TABLE... RENAME... expression
6547
6548    Args:
6549        old_name: The old name of the table
6550        new_name: The new name of the table
6551
6552    Returns:
6553        Alter table expression
6554    """
6555    old_table = to_table(old_name)
6556    new_table = to_table(new_name)
6557    return AlterTable(
6558        this=old_table,
6559        actions=[
6560            RenameTable(this=new_table),
6561        ],
6562    )
6563
6564
6565def convert(value: t.Any, copy: bool = False) -> Expression:
6566    """Convert a python value into an expression object.
6567
6568    Raises an error if a conversion is not possible.
6569
6570    Args:
6571        value: A python object.
6572        copy: Whether or not to copy `value` (only applies to Expressions and collections).
6573
6574    Returns:
6575        Expression: the equivalent expression object.
6576    """
6577    if isinstance(value, Expression):
6578        return maybe_copy(value, copy)
6579    if isinstance(value, str):
6580        return Literal.string(value)
6581    if isinstance(value, bool):
6582        return Boolean(this=value)
6583    if value is None or (isinstance(value, float) and math.isnan(value)):
6584        return NULL
6585    if isinstance(value, numbers.Number):
6586        return Literal.number(value)
6587    if isinstance(value, datetime.datetime):
6588        datetime_literal = Literal.string(
6589            (value if value.tzinfo else value.replace(tzinfo=datetime.timezone.utc)).isoformat()
6590        )
6591        return TimeStrToTime(this=datetime_literal)
6592    if isinstance(value, datetime.date):
6593        date_literal = Literal.string(value.strftime("%Y-%m-%d"))
6594        return DateStrToDate(this=date_literal)
6595    if isinstance(value, tuple):
6596        return Tuple(expressions=[convert(v, copy=copy) for v in value])
6597    if isinstance(value, list):
6598        return Array(expressions=[convert(v, copy=copy) for v in value])
6599    if isinstance(value, dict):
6600        return Map(
6601            keys=Array(expressions=[convert(k, copy=copy) for k in value]),
6602            values=Array(expressions=[convert(v, copy=copy) for v in value.values()]),
6603        )
6604    raise ValueError(f"Cannot convert {value}")
6605
6606
6607def replace_children(expression: Expression, fun: t.Callable, *args, **kwargs) -> None:
6608    """
6609    Replace children of an expression with the result of a lambda fun(child) -> exp.
6610    """
6611    for k, v in expression.args.items():
6612        is_list_arg = type(v) is list
6613
6614        child_nodes = v if is_list_arg else [v]
6615        new_child_nodes = []
6616
6617        for cn in child_nodes:
6618            if isinstance(cn, Expression):
6619                for child_node in ensure_collection(fun(cn, *args, **kwargs)):
6620                    new_child_nodes.append(child_node)
6621                    child_node.parent = expression
6622                    child_node.arg_key = k
6623            else:
6624                new_child_nodes.append(cn)
6625
6626        expression.args[k] = new_child_nodes if is_list_arg else seq_get(new_child_nodes, 0)
6627
6628
6629def column_table_names(expression: Expression, exclude: str = "") -> t.Set[str]:
6630    """
6631    Return all table names referenced through columns in an expression.
6632
6633    Example:
6634        >>> import sqlglot
6635        >>> sorted(column_table_names(sqlglot.parse_one("a.b AND c.d AND c.e")))
6636        ['a', 'c']
6637
6638    Args:
6639        expression: expression to find table names.
6640        exclude: a table name to exclude
6641
6642    Returns:
6643        A list of unique names.
6644    """
6645    return {
6646        table
6647        for table in (column.table for column in expression.find_all(Column))
6648        if table and table != exclude
6649    }
6650
6651
6652def table_name(table: Table | str, dialect: DialectType = None, identify: bool = False) -> str:
6653    """Get the full name of a table as a string.
6654
6655    Args:
6656        table: Table expression node or string.
6657        dialect: The dialect to generate the table name for.
6658        identify: Determines when an identifier should be quoted. Possible values are:
6659            False (default): Never quote, except in cases where it's mandatory by the dialect.
6660            True: Always quote.
6661
6662    Examples:
6663        >>> from sqlglot import exp, parse_one
6664        >>> table_name(parse_one("select * from a.b.c").find(exp.Table))
6665        'a.b.c'
6666
6667    Returns:
6668        The table name.
6669    """
6670
6671    table = maybe_parse(table, into=Table, dialect=dialect)
6672
6673    if not table:
6674        raise ValueError(f"Cannot parse {table}")
6675
6676    return ".".join(
6677        part.sql(dialect=dialect, identify=True, copy=False)
6678        if identify or not SAFE_IDENTIFIER_RE.match(part.name)
6679        else part.name
6680        for part in table.parts
6681    )
6682
6683
6684def normalize_table_name(table: str | Table, dialect: DialectType = None, copy: bool = True) -> str:
6685    """Returns a case normalized table name without quotes.
6686
6687    Args:
6688        table: the table to normalize
6689        dialect: the dialect to use for normalization rules
6690        copy: whether or not to copy the expression.
6691
6692    Examples:
6693        >>> normalize_table_name("`A-B`.c", dialect="bigquery")
6694        'A-B.c'
6695    """
6696    from sqlglot.optimizer.normalize_identifiers import normalize_identifiers
6697
6698    return ".".join(
6699        p.name
6700        for p in normalize_identifiers(
6701            to_table(table, dialect=dialect, copy=copy), dialect=dialect
6702        ).parts
6703    )
6704
6705
6706def replace_tables(
6707    expression: E, mapping: t.Dict[str, str], dialect: DialectType = None, copy: bool = True
6708) -> E:
6709    """Replace all tables in expression according to the mapping.
6710
6711    Args:
6712        expression: expression node to be transformed and replaced.
6713        mapping: mapping of table names.
6714        dialect: the dialect of the mapping table
6715        copy: whether or not to copy the expression.
6716
6717    Examples:
6718        >>> from sqlglot import exp, parse_one
6719        >>> replace_tables(parse_one("select * from a.b"), {"a.b": "c"}).sql()
6720        'SELECT * FROM c /* a.b */'
6721
6722    Returns:
6723        The mapped expression.
6724    """
6725
6726    mapping = {normalize_table_name(k, dialect=dialect): v for k, v in mapping.items()}
6727
6728    def _replace_tables(node: Expression) -> Expression:
6729        if isinstance(node, Table):
6730            original = normalize_table_name(node, dialect=dialect)
6731            new_name = mapping.get(original)
6732
6733            if new_name:
6734                table = to_table(
6735                    new_name,
6736                    **{k: v for k, v in node.args.items() if k not in TABLE_PARTS},
6737                )
6738                table.add_comments([original])
6739                return table
6740        return node
6741
6742    return expression.transform(_replace_tables, copy=copy)
6743
6744
6745def replace_placeholders(expression: Expression, *args, **kwargs) -> Expression:
6746    """Replace placeholders in an expression.
6747
6748    Args:
6749        expression: expression node to be transformed and replaced.
6750        args: positional names that will substitute unnamed placeholders in the given order.
6751        kwargs: keyword arguments that will substitute named placeholders.
6752
6753    Examples:
6754        >>> from sqlglot import exp, parse_one
6755        >>> replace_placeholders(
6756        ...     parse_one("select * from :tbl where ? = ?"),
6757        ...     exp.to_identifier("str_col"), "b", tbl=exp.to_identifier("foo")
6758        ... ).sql()
6759        "SELECT * FROM foo WHERE str_col = 'b'"
6760
6761    Returns:
6762        The mapped expression.
6763    """
6764
6765    def _replace_placeholders(node: Expression, args, **kwargs) -> Expression:
6766        if isinstance(node, Placeholder):
6767            if node.name:
6768                new_name = kwargs.get(node.name)
6769                if new_name:
6770                    return convert(new_name)
6771            else:
6772                try:
6773                    return convert(next(args))
6774                except StopIteration:
6775                    pass
6776        return node
6777
6778    return expression.transform(_replace_placeholders, iter(args), **kwargs)
6779
6780
6781def expand(
6782    expression: Expression,
6783    sources: t.Dict[str, Subqueryable],
6784    dialect: DialectType = None,
6785    copy: bool = True,
6786) -> Expression:
6787    """Transforms an expression by expanding all referenced sources into subqueries.
6788
6789    Examples:
6790        >>> from sqlglot import parse_one
6791        >>> expand(parse_one("select * from x AS z"), {"x": parse_one("select * from y")}).sql()
6792        'SELECT * FROM (SELECT * FROM y) AS z /* source: x */'
6793
6794        >>> expand(parse_one("select * from x AS z"), {"x": parse_one("select * from y"), "y": parse_one("select * from z")}).sql()
6795        'SELECT * FROM (SELECT * FROM (SELECT * FROM z) AS y /* source: y */) AS z /* source: x */'
6796
6797    Args:
6798        expression: The expression to expand.
6799        sources: A dictionary of name to Subqueryables.
6800        dialect: The dialect of the sources dict.
6801        copy: Whether or not to copy the expression during transformation. Defaults to True.
6802
6803    Returns:
6804        The transformed expression.
6805    """
6806    sources = {normalize_table_name(k, dialect=dialect): v for k, v in sources.items()}
6807
6808    def _expand(node: Expression):
6809        if isinstance(node, Table):
6810            name = normalize_table_name(node, dialect=dialect)
6811            source = sources.get(name)
6812            if source:
6813                subquery = source.subquery(node.alias or name)
6814                subquery.comments = [f"source: {name}"]
6815                return subquery.transform(_expand, copy=False)
6816        return node
6817
6818    return expression.transform(_expand, copy=copy)
6819
6820
6821def func(name: str, *args, copy: bool = True, dialect: DialectType = None, **kwargs) -> Func:
6822    """
6823    Returns a Func expression.
6824
6825    Examples:
6826        >>> func("abs", 5).sql()
6827        'ABS(5)'
6828
6829        >>> func("cast", this=5, to=DataType.build("DOUBLE")).sql()
6830        'CAST(5 AS DOUBLE)'
6831
6832    Args:
6833        name: the name of the function to build.
6834        args: the args used to instantiate the function of interest.
6835        copy: whether or not to copy the argument expressions.
6836        dialect: the source dialect.
6837        kwargs: the kwargs used to instantiate the function of interest.
6838
6839    Note:
6840        The arguments `args` and `kwargs` are mutually exclusive.
6841
6842    Returns:
6843        An instance of the function of interest, or an anonymous function, if `name` doesn't
6844        correspond to an existing `sqlglot.expressions.Func` class.
6845    """
6846    if args and kwargs:
6847        raise ValueError("Can't use both args and kwargs to instantiate a function.")
6848
6849    from sqlglot.dialects.dialect import Dialect
6850
6851    dialect = Dialect.get_or_raise(dialect)
6852
6853    converted: t.List[Expression] = [maybe_parse(arg, dialect=dialect, copy=copy) for arg in args]
6854    kwargs = {key: maybe_parse(value, dialect=dialect, copy=copy) for key, value in kwargs.items()}
6855
6856    constructor = dialect.parser_class.FUNCTIONS.get(name.upper())
6857    if constructor:
6858        if converted:
6859            if "dialect" in constructor.__code__.co_varnames:
6860                function = constructor(converted, dialect=dialect)
6861            else:
6862                function = constructor(converted)
6863        elif constructor.__name__ == "from_arg_list":
6864            function = constructor.__self__(**kwargs)  # type: ignore
6865        else:
6866            constructor = FUNCTION_BY_NAME.get(name.upper())
6867            if constructor:
6868                function = constructor(**kwargs)
6869            else:
6870                raise ValueError(
6871                    f"Unable to convert '{name}' into a Func. Either manually construct "
6872                    "the Func expression of interest or parse the function call."
6873                )
6874    else:
6875        kwargs = kwargs or {"expressions": converted}
6876        function = Anonymous(this=name, **kwargs)
6877
6878    for error_message in function.error_messages(converted):
6879        raise ValueError(error_message)
6880
6881    return function
6882
6883
6884def case(
6885    expression: t.Optional[ExpOrStr] = None,
6886    **opts,
6887) -> Case:
6888    """
6889    Initialize a CASE statement.
6890
6891    Example:
6892        case().when("a = 1", "foo").else_("bar")
6893
6894    Args:
6895        expression: Optionally, the input expression (not all dialects support this)
6896        **opts: Extra keyword arguments for parsing `expression`
6897    """
6898    if expression is not None:
6899        this = maybe_parse(expression, **opts)
6900    else:
6901        this = None
6902    return Case(this=this, ifs=[])
6903
6904
6905def cast_unless(
6906    expression: ExpOrStr,
6907    to: DATA_TYPE,
6908    *types: DATA_TYPE,
6909    **opts: t.Any,
6910) -> Expression | Cast:
6911    """
6912    Cast an expression to a data type unless it is a specified type.
6913
6914    Args:
6915        expression: The expression to cast.
6916        to: The data type to cast to.
6917        **types: The types to exclude from casting.
6918        **opts: Extra keyword arguments for parsing `expression`
6919    """
6920    expr = maybe_parse(expression, **opts)
6921    if expr.is_type(*types):
6922        return expr
6923    return cast(expr, to, **opts)
6924
6925
6926def true() -> Boolean:
6927    """
6928    Returns a true Boolean expression.
6929    """
6930    return Boolean(this=True)
6931
6932
6933def false() -> Boolean:
6934    """
6935    Returns a false Boolean expression.
6936    """
6937    return Boolean(this=False)
6938
6939
6940def null() -> Null:
6941    """
6942    Returns a Null expression.
6943    """
6944    return Null()
6945
6946
6947# TODO: deprecate this
6948TRUE = Boolean(this=True)
6949FALSE = Boolean(this=False)
6950NULL = Null()